count distinct if 分析

最新推荐文章于 2025-05-18 11:59:27 发布
原创 最新推荐文章于 2025-05-18 11:59:27 发布 · 9.7k 阅读 · 6 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

我们稍做修改

1

2

3

4

5

select partition_date,count(user_id),

         count(distinct if(user_is_new = 1, user_id, 0))  --注意新增用户量的统计,加了distinct去重

from dw.nice_live_dw_user_active_day

where location_city like '%xxx%' and partition_date >= 20180901

group by partition_date;

结果如下

1

20180901    16737  261

 这次看着就像是对了吧,我们加了distinct进行去重

每次来一条数据先过if()然后再进行去重最后统计.但是实际上结果依旧是错误的.

我们来模拟一下筛选统计的过程

我们有这样四条数据

user_id    user_is_new

   1               1

   2              0

   3              1

   4              0

表中的数据是一条一条遍历的,

(1)当user_id = 1的数据过来的时候,我们先过if函数  user_is_new = 1    ==>  count(distinct user_id = 1), 

然后我们把user_id = 1进行重复判断,我们用一个模拟容器来模拟去重,

从容器里找user_id = 1的数据,发现没有,不重复,所以通过我们把count+1,然后把user_id = 1的数据放入,用于下条去重

(2)当user_id = 2的数据过来的时候,我们先过if函数  user_is_new = 0    ==>  count(distinct 0), 

然后我们把0进行重复判断,

从容器里找0的数据,发现没有,不重复,所以通过我们把count+1,然后把0的数据放入,用于下条去重

(3)当user_id = 3的数据过来的时候,我们先过if函数  user_is_new = 1    ==>  count(distinct user_id = 3), 

然后我们把user_id = 3进行重复判断,

从容器里找user_id = 3的数据,发现没有,不重复,所以通过我们把count+1,然后把user_id = 3的数据放入,用于下条去重

(4)当user_id = 4的数据过来的时候,我们先过if函数  user_is_new = 0    ==>  count(distinct 0), 

然后我们把0进行重复判断,

从容器里找0的数据,发现重复,是之前user_id = 2的时候过if()转化成0的那条数据,所以count不执行

我们通过模拟count(distinct if)过程发现,在count的时候我们把不符合条件的最开始的那条语句也count进去了一次

导致最终结果比正确结果多了1.

我们在原基础语句上再减去1就是正确的hql语句

其实在日常中我们做分类筛选统计的时候一般是用sum来完成的,符合条件sum+1,不符合条件sum+0

1

2

3

4

5

select partition_date,count(user_id),

         sum(if(user_is_new = 1, 1, 0))  --用sum进行筛选统计

from dw.nice_live_dw_user_active_day

where location_city like '%xxx%' and partition_date >= 20180901

group by partition_date;

 结果如下

1

20180901    16737  260

 sum(if)只试用于单个条件判断,如果筛选条件很多,我们可以用sum(case when then else end)来进行多条件筛选

注意,hive中并没有sum(distinct col1)这种使用方式,我们可以使用sum(col) group by col来达到相同效果.

 

count(distinct if(user_is_new = 1, user_id, null)) //这样统计结果就不会多1了

文森特vit
关注
Hive count(distinct c1) ，不包含NULL的总数
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
05-31 1136
select count(distinct c1) from ( SELECT if(1=1,null,0) as c1 union ALL select 2 as c1 union ALL select 2 as c1 union ALL select 3 as c1 )t0 -- 2 select count(distinct c1) from ( SELECT if(1=1,null,0) as c1 union ALL SELECT if(2=2,null,0) as c1 ...
mysql sumif distinct_【hive】count() count(if) count(distinct if) sum(if)的区别
weixin_39996750的博客
02-10 1401
表名:user_active_day (用户日活表)表内容:user_id(用户id) user_is_new(是否新用户 1:新增用户 0:老用户) location_city(用户所在地区)partition_date(日期分区)需求:找出20180901至今的xxx地区的用户日活量以及新增用户量思路:筛选日期分区和地区,统计user_id的数量为用户日活量,统计user_is_new...
count(distinct if(a.business_type in (10, 11), a.complain_id, null)) 同时使用的逻辑
m0_53862381的博客
03-07 767
SQL：条件计数与聚合：从基础到高级的完整指南
weixin_72058778的博客
05-18 638
本文介绍了SQL中实现条件计数和聚合的各种方法，从基础的COUNT+IF组合到高级的多条件统计技巧。简单条件计数优先使用COUNT(IF)或SUM(CASE WHEN)复杂多条件统计使用CASE WHEN表达式PostgreSQL用户可以利用FILTER语法提高效率注意不同方法的性能差异，合理优化查询。
distinct if count搭配使用
mmdsl083的博客
11-30 1554
根据某列状态统计数量，并且去重 DISTINCT是为了演示联表的时候重复数据情况，单表的话是不用DISTINCT的。
Select count distinct if
PentaQwQ的博客
04-27 699
count函数具有三种形式： count(*) count(expression) count(distinct expression) count(*) 该COUNT(*)函数返回SELECT语句返回的结果集中的行数。返回行数，COUNT(*)包括重复、非 NULL 和NULL行。 count(expression) 作为表达式的结果，COUNT(expression)返回不包含NULL值的行数。 COUNT(DISTINCT expression) COUNT(DISTINCT expression
mysql count if distinct
热门推荐
johnhuster的专栏
04-12 1万+
mysql count if distinct
Tidb 跨分区使用count distinct if 统计不准（有bug）
lvqinglou的博客
09-15 837
我们公司使用了tidb，然后我就发现了这东西有点不靠谱，用新技术果然要付出代价。不要问我为什么用，反正很蛋疼。
sparksql源码系列 | 一文搞懂with one count distinct 执行原理
xiaoluobutou的专栏
02-16 617
在面试时，或多或少会被问到有关count distinct的优化，现在离线任务用到的基本就是hivesql和sparksql，那sparksql中有关count distinct做了哪些优化呢？
distinct if
08-24
- *1* *2* *3* [count distinct if 分析](https://blog.youkuaiyun.com/qq_17902567/article/details/89031595)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_...
在Apache Kylin中使用Count Distinct
u014582551的博客
08-04 584
雷顿学院大数据：http://www.leidun.site/在OLAP多维分析中，Count Distinct（去重计数）是一种非常常用的指标度量，比如一段时间内的UV、活跃用户数等等;从1.5.3开始，Apache Kylin提供了两种Count Distinct计算方式，一种是近似的，一种是精确的，精确的Count Distinct指标在Build时候会消耗更多的资源（内存和存储），Buil...
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;   namespace SQLReportTool
{
    public partial class Form1 : Form
    {
        private string uid;
        private string pwd;
        private Dictionary<string, string> alarmLevelMap = new Dictionary<string, string>();           public Form1()
        {
            InitializeComponent();
            for (int i = 1; i <= 30; i++) cmbDays.Items.Add(i.ToString());
            cmbDays.SelectedIndex = 6;
            LoadCredentials();
        }           private void LoadCredentials()
        {
            try
            {
                var lines = File.ReadAllLines("Password.txt");
                foreach (var line in lines)
                {
                    if (line.StartsWith("UID=")) uid = line.Substring(4).Trim();
                    if (line.StartsWith("PWD=")) pwd = line.Substring(4).Trim();
                }                   if (string.IsNullOrWhiteSpace(uid) || string.IsNullOrWhiteSpace(pwd))
                    throw new Exception("Password.txt 缺少 UID 或 PWD。");
            }
            catch (Exception ex)
            {
                MessageBox.Show("讀取 Password.txt 失敗: " + ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Exit();
            }
        }           private int GetSelectedDays()
        {
            return Convert.ToInt32(cmbDays.SelectedItem);
        }           private async void btnMTBA_Click(object sender, EventArgs e)
        {
            int days = GetSelectedDays();
            string sql = $@"
                SELECT TOP 1000 EQMYEAR, EQMEF, PRDTIME, EQPID, EQPTYPE, STAGE, MTBA_F
                FROM DVI.dbo.EQM_CIM_EQMTSUM_FIX_MTYPE_V
                WHERE PRODAREA = 'MF27'
                  AND CREATION_DT >= DATEADD(DAY, -{days}, GETDATE())
                ORDER BY EQMYEAR DESC, EQMMONTH DESC, EQMDD DESC";               await RunQueryAsync(sql, "MTBA 報表");
        }           private async void btnDispatch_Click(object sender, EventArgs e)
        {
            int days = GetSelectedDays();
            string sql = $@"
                SELECT TOP 1000 DWLNO, DWLMCNO, DWLEQID, DWLITEM, DWLCLASS, DWLOPID, DWLASTIME, DWLACTIME, DWLSTATUS
                FROM DVI.dbo.MMSTDWL_V
                WHERE DWLDEP = 'MF27'
                  AND DWLCLASS IN ('修機', '換部品')
                  AND DWLCREATE >= DATEADD(DAY, -{days}, GETDATE())
                ORDER BY DWLCREATE DESC";               await RunQueryAsync(sql, "派工報表");
        }           private async void btnAlarmLevel_Click(object sender, EventArgs e)
        {
            lblStatus.Text = "查詢 AlarmLevel 中...";
            Cursor = Cursors.WaitCursor;
            int days = GetSelectedDays();
            List<string> stages = new List<string>();
            List<string> eqchars = new List<string>();               try
            {
                using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
                {
                    conn.Open();
                    string rawStageSql = $@"
                        SELECT DISTINCT LTRIM(RTRIM(STAGE)) AS STAGE, LTRIM(RTRIM(EQP_EQCHAR)) AS EQCHAR
                        FROM DVI.dbo.EQM_CIM_EQPALARM_RAW_V
                        WHERE DEPT = 'MF27'
                          AND STAGE IS NOT NULL AND LTRIM(RTRIM(STAGE)) <> ''
                          AND EQP_EQCHAR IS NOT NULL AND LTRIM(RTRIM(EQP_EQCHAR)) <> ''
                          AND ALARM_TIME >= DATEADD(DAY, -{days}, GETDATE())";                       using (var cmd = new OdbcCommand(rawStageSql, conn))
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string stage = reader["STAGE"].ToString();
                            string eqchar = reader["EQCHAR"].ToString();
                            if (!stages.Contains(stage)) stages.Add(stage);
                            if (!eqchars.Contains(eqchar)) eqchars.Add(eqchar);
                        }
                    }
                }                   if (stages.Count == 0 || eqchars.Count == 0)
                {
                    MessageBox.Show("AlarmRaw 中無任何 STAGE 或 EQCHAR 記錄。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    lblStatus.Text = "查詢完成（無 STAGE/EQCHAR）";
                    return;
                }                   string stageInSql = string.Join(",", stages.ConvertAll(s => $"'{s.Replace("'", "''")}'"));
                string eqcharInSql = string.Join(",", eqchars.ConvertAll(s => $"'{s.Replace("'", "''")}'"));                   string levelSql = $@"
                    SELECT *, 'MF27_' + STAGE + '_' + EQCHAR + '_' + ALARM_ID AS A欄
                    FROM DVI.dbo.MST_CIM_ALARM_LEVEL_MAPPING_V
                    WHERE STAGE IN ({stageInSql}) AND EQCHAR IN ({eqcharInSql})";                   await Task.Run(() =>
                {
                    using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
                    {
                        conn.Open();
                        using (var da = new OdbcDataAdapter(levelSql, conn))
                        {
                            DataTable dt = new DataTable();
                            da.Fill(dt);
                            alarmLevelMap.Clear();
                            foreach (DataRow row in dt.Rows)
                            {
                                string key = row["A欄"].ToString();
                                string level = row["ALARM_LEVEL"]?.ToString() ?? "NA";
                                if (!alarmLevelMap.ContainsKey(key))
                                    alarmLevelMap.Add(key, level);
                            }                               dgvResult.Invoke(new MethodInvoker(() => dgvResult.DataSource = dt));
                            lblStatus.Invoke(new MethodInvoker(() => lblStatus.Text = $"查詢完成，共 {dt.Rows.Count} 筆。"));
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                lblStatus.Text = "查詢 AlarmLevel 失敗：" + ex.Message;
                MessageBox.Show(ex.ToString(), "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                Cursor = Cursors.Default;
            }
        }           private async void btnAlarmRaw_Click(object sender, EventArgs e)
        {
            int days = GetSelectedDays();
            string sql = $@"
       SELECT TOP 1000 EQP_ID, EQP_EQCHAR, ALARM_ID, EVENT_ID, ALARM_TYPE, ALARM_LEVEL, ALARM_MSG, ALARM_TIME, STAGE
       FROM DVI.dbo.EQM_CIM_EQPALARM_RAW_V
       WHERE DEPT = 'MF27'
         AND ALARM_TYPE = 'S'
         AND ALARM_TIME >= DATEADD(DAY, -{days}, GETDATE())
       ORDER BY ALARM_TIME DESC";
            await Task.Run(() =>
            {
                using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
                {
                    conn.Open();
                    using (var da = new OdbcDataAdapter(sql, conn))
                    {
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dt.Columns.Add("等級比對", typeof(string));
                        // 新增一個 List 來存放符合條件的資料列
                        List<DataRow> rowsToKeep = new List<DataRow>();
                        foreach (DataRow row in dt.Rows)
                        {
                            string key = $"MF27_{row["STAGE"]}_{row["EQP_EQCHAR"]}_{row["ALARM_ID"]}";
                            string level = alarmLevelMap.ContainsKey(key) ? alarmLevelMap[key] : "NA";
                            row["等級比對"] = level;
                            // 只保留 "Failure"
                            if (level == "Failure")
                                rowsToKeep.Add(row);
                        } // 建立新資料表，匯入符合的列
                        DataTable filtered = dt.Clone(); // 複製欄位結構
                        foreach (var row in rowsToKeep)
                            filtered.ImportRow(row);
                        dgvResult.Invoke(new MethodInvoker(() => dgvResult.DataSource = filtered));
                        lblStatus.Invoke(new MethodInvoker(() => lblStatus.Text = $"查詢完成，共 {filtered.Rows.Count} 筆（等級比對 = Failure）。"));
                    }
                }
            });
        }           private async void btnSPC_Click(object sender, EventArgs e)
        {
            int days = GetSelectedDays();
            string sql = $@"
                SELECT TOP 100 LOTID, MEASURE_DATE, METHOD, EQUIPMENT, USER_ID,
                               OCCASION, ERROR_MSG, SPC_RESULT, OCAPNO, TRANSFER_DATE
                FROM DVI.dbo.PRD_CIM_SPC_Data_V
                WHERE MEASURE_DATE >= DATEADD(DAY, -{days}, GETDATE())
                 
                  AND SPC_RESULT NOT IN ('OK','NG')";
            //AND PRODAREA IN('27')
            // AND EQUIPMENT like 'R%'               await RunQueryAsync(sql, "SPC 報表");
        }           private async Task RunQueryAsync(string sql, string label)
        {
            lblStatus.Text = $"查詢 {label} 中...";
            Cursor = Cursors.WaitCursor;
            var sw = System.Diagnostics.Stopwatch.StartNew();               try
            {
                await Task.Run(() =>
                {
                    using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
                    {
                        conn.Open();
                        using (var da = new OdbcDataAdapter(sql, conn))
                        {
                            DataTable dt = new DataTable();
                            da.Fill(dt);
                            dgvResult.Invoke(new MethodInvoker(() => dgvResult.DataSource = dt));
                            lblStatus.Invoke(new MethodInvoker(() =>
                            {
                                lblStatus.Text = dt.Rows.Count == 0
                                    ? $"{label}：查詢完成但無資料。"
                                    : $"{label}：共 {dt.Rows.Count} 筆，耗時 {sw.ElapsedMilliseconds} ms";
                            }));
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                lblStatus.Text = $"{label} 查詢失敗：" + ex.Message;
                MessageBox.Show(ex.ToString(), "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                Cursor = Cursors.Default;
            }
        }
        private async void btnAGS_Click(object sender, EventArgs e)
        {
           /* lblStatus.Text = "查詢AGS 中...";
            int days = GetSelectedDays(); // 你應已有這個方法從 cmbDays 取得天數
            string sql = $@"
       SELECT
           SPILDT, EQPID, EQPCHAR, EQPTYP, BRANDCODE,
           CHGSDT, CHGEDT, STATUS, EMPID, STATUSDESC
       FROM DVI.dbo.DVI_EQM_CIM_EQPS_V
       WHERE SPILDT >= DATEADD(DAY, -{days}, GETDATE())
         AND EQPTYP LIKE 'R%'";
            await Task.Run(() =>
            {
                using (var conn = new OdbcConnection($"DSN=dw323;UID={uid};PWD={pwd};"))
                {
                    conn.Open();
                    using (var da = new OdbcDataAdapter(sql, conn))
                    {
                        DataTable rawTable = new DataTable();
                        da.Fill(rawTable);
                        // 用來儲存合併後資料
                        DataTable mergedTable = rawTable.Clone();
                        DataRow current = null;
                        foreach (DataRow row in rawTable.Rows)
                        {
                            if (current == null)
                            {
                                current = mergedTable.NewRow();
                                current.ItemArray = row.ItemArray.Clone() as object[];
                            }
                            else
                            {
                                // 比對 EQPID 和 STATUSDESC 是否相同
                                bool isSameGroup =
                                    current["EQPID"].ToString() == row["EQPID"].ToString() &&
                                    current["STATUSDESC"].ToString() == row["STATUSDESC"].ToString();
                                if (isSameGroup)
                                {
                                    // 更新結束時間 CHGEDT 為目前行的 CHGEDT
                                    current["CHGEDT"] = row["CHGEDT"];
                                }
                                else
                                {
                                    // 新分段，加入前一筆資料
                                    mergedTable.Rows.Add(current);
                                    current = mergedTable.NewRow();
                                    current.ItemArray = row.ItemArray.Clone() as object[];
                                }
                            }
                        }
                        if (current != null)
                            mergedTable.Rows.Add(current); // 加入最後一筆
                        dgvResult.Invoke(new MethodInvoker(() => dgvResult.DataSource = mergedTable));
                        lblStatus.Invoke(new MethodInvoker(() => lblStatus.Text = $"查詢完成，共 {mergedTable.Rows.Count} 筆（已合併）。"));
                    }
                }
            });
        */}
}
    }       1.btnSPC_Click 讀取讀取時間要拉長不然會出現逾時
2.ALARM LEVEL跟ALARM RAW 幫我整併(ALARM RAW會用到ALARMLEVEL的東西)
3.後續剩下MTBA/派工/AlarmRaw/SPC/AGS 分天匯出TXT(包含TITLE) 需求如上 直接幫我設計.cs跟 desinger
最新发布
06-26
我们正在处理一个C#WinForms应用程序的优化任务，需要完成以下三个主要目标：1.延长SPC查询的超时时间。2.合并AlarmLevel与AlarmRaw数据。3.将多个查询结果（MTBA、Dispatch、AlarmRaw、SPC、AGS）导出为带标题的TXT...
MySql查询语句之count函数中distinct的使用
weixin_38669513的博客
06-20 5838
在开发过程中遇到以下场景的sql查询，一个手机号代表一个用户，一个用户可以对车进行多次估价，每次估价在估价记录表中都会被记录，估价之后可以选择是否订阅，如果该用户之前订阅过就会在订阅表中将之前的数据覆盖掉，如果没有订阅过就会重新插入一条数据，所以在估价记录表中一个手机号可能会有多条订阅记录，所以在统计订阅用户数的时候就要根据手机号对其去重，subscribe_yn字段标识用户是否订阅，所以可以用s...
dataset的去重计数 g2_统计符合条件的去重过的数量 - - count distinct if case
weixin_27058835的博客
12-30 215
现有表结构：CREATE TABLE `example_dataset` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`tel` bigint(11) DEFAULT NULL,`gender` varchar(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 D...
SQL count distinct与NULL
chncaesar的专栏
01-02 4302
$sh@ORCL> select * from tt2; TT2_ID TT2_NAME TT2_DATE ---------- ---------- --------- 1 1 23-DEC-13 2 23-DEC-13 3 23-DEC-13 4
sql中sum和count 以及 count(distinct )嵌套条件语句的用法
licux的博客
12-11 4571
sum是对选择的列的 每一条数据进行求和处理 count 是统计符合条件的列数 对于数据库中为空的值来说 ，两者都不计算 SELECT s.id,s.user_id, s.service_name, s.service_path, s.service_precision, s.concurrent_number, count(DISTINCT c.user_id) as history_user ,count(DISTINCT if(c.use_status = 1 , c.use
SQL中distinct去重关键字的使用和count统计组合的使用
睡不醒的每天
06-13 4765
distinct必须在列的前面，否则直接提示错误，当distinct作用于单列的的情况下，统计查询的结果为行数目的总数，包含null为空的数目。count(distinct 列名)：根据指定的列统计记录总数，不包含重复的记录，不包括NULL或空的值。select distinct 列名1，列名2，列名3…select distinct 列名 from 表；也可以使用count(2)、count(3)等等。count(distinct 列名)
mysql distinct去重，IFNULL空值处理
New__Horizons的博客
11-27 2778
mysql distinct去重，IFNULL空值处理
【SQL】COUNT()函数 用法详解
Rudy的博客
03-27 1万+
COUNT()函数里边不能用查询语句啊！
distinct if count
03-28
distinct if count是一种在数据库中使用的查询语句，用于计算满足特定条件的不同值的数量。它可以帮助我们对数据进行分类和统计分析。 具体来说，distinct if count语句的语法如下： SELECT COUNT(DISTINCT column...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值