snowflake-cloud-data-platform - 只是想检查这两个查询是否给出相同的结果或不同的结果

Question

查询 1:

   SELECT id,COUNT(X.C_NO) as count
    FROM table X
    WHERE X.date = '2022-02-02'
    and P_NO is not null
    group by id;

查询 2:

SELECT   id,
         sum(CASE WHEN C_NO IS NOT NULL and P_NO is not null THEN 1 ELSE 0 END) as count
         FROM table X
WHERE B.date = '2022-02-02'
group by id;

只是想知道这两个查询会产生相同的结果还是不同的结果

Answer 1

这些查询并不等价，它们会产生不同的结果。示例:

CREATE TABLE tab
AS
SELECT 1 AS id, NULL AS P_NO, '2022-02-02'::DATE AS date, 10 AS c_no


SELECT id,COUNT(X.C_NO) as count
FROM tab X
WHERE X.date = '2022-02-02'
  and P_NO is not null
group by id;

SELECT   id,
 sum(CASE WHEN C_NO IS NOT NULL and P_NO is not null THEN 1 ELSE 0 END) as count
FROM tab X
WHERE X.date = '2022-02-02'
group by id;

db<>fiddle demo

输出:

Key子句是在GROUP BY之前执行的WHERE子句。有些行甚至在有机会被分组之前就被过滤掉了。

---

旁注:

第二个查询是条件聚合的示例，可以使用 COUNT_IF 聚合函数进一步简化:

SELECT id,
   COUNF_IF(C_NO IS NOT NULL and P_NO is not null) as count
FROM tab X
WHERE X.date = '2022-02-02'
group by id;