我有一張桌子：
Date | ID | Company | Click
----------- ---- --------- --------
01/01/2021 | 01 | Us | 1
01/01/2021 | 01 | Us | 1
01/01/2021 | 01 | Other | 1
01/01/2021 | 02 | Us | 0
01/01/2021 | 02 | Other | 0
02/01/2021 | 03 | Us | 1
02/01/2021 | 03 | Us | 1
02/01/2021 | 04 | Us | 0
我想按日期分組并計數：每天有多少個不同的 ID，有多少個唯一IDclicked=1以及Company="Us"
我目前的代碼是：
create table grouped as
select date
, count(distinct ID) as ID_count
, sum(case when company="Us" and clicked=1 then 1 else 0 end) as Click_count
from have
group by 1
結果應如下所示：
Date | ID_count | Click_count
----------- ---------- ------------
01/01/2021 | 2 | 1
02/01/2021 | 2 | 1
您會注意到我的代碼計算了重復的 ID，因此該click_count列在兩個日期中都取值為 2。我該如何解決？
uj5u.com熱心網友回復：
您應該使用運算式COUNT()來計算不同的IDs CASE：
COUNT(DISTINCT CASE WHEN company = 'Us' AND clicked = 1 THEN ID END) AS click_count
uj5u.com熱心網友回復：
使用filter條件聚合的PostgreSQL 解決方案：
select date,
count(distinct id) id_count,
count(distinct id) filter (where click = 1 and company = 'Us') click_count
from the_table
group by date;
如果您的資料庫缺乏條件聚合filter功能，則可以使用標量子查詢（@forpas 建議的替代方案）。
select date,
count(distinct id) id_count,
(
select count(distinct id)
from the_table
where click = 1 and company = 'Us' and date = t.date
) click_count
from the_table t
group by date;
SQL小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/370191.html