snowflake-cloud-data-platform - Snowflake - 如何创建包含唯一记录的汇总表

Question

我正在寻找有关如何从现有表生成汇总表或 View 的一些 Snowflake 语法帮助。我的汇总表应该有来自现有表的每个唯一 ID 的 1 行以及指示是否已达到各种里程碑(根据汇总列名称)的 bool 值。由于我是 Snowflake 新手，因此感谢您的帮助。谢谢。

Existing Table

Desired Summary Table/View

Answer 1

所以用Himanshu的数据，谢谢:

WITH fake_data(id, updated, pipeline_id, stage_id) AS (
    SELECT column1, to_date(column2,'mm/dd/yyyy hh:mm:ss'), column3, column4
    FROM VALUES
        (1111, '02/01/2022 09:01:00', 'A', '1' ),
        (1111, '02/01/2022 10:01:00', 'A', '2' ),
        (1111, '02/01/2022 11:01:00', 'B', '5' ),                          
        (2222, '02/02/2022 13:01:00', 'A', '1' ),
        (2222, '02/03/2022 18:01:00', 'B', '5' ),
        (2222, '02/04/2022 07:01:00', 'B', '6' ),                                         
        (3333, '02/02/2022 14:01:00', 'A', '1' ),
        (3333, '02/03/2022 18:01:00', 'A', '2' ),
        (3333, '02/03/2022 07:01:00', 'C', '7' ),
        (3333, '02/03/2022 21:01:00', 'C', '8' ),
        (3333, '02/05/2022 17:01:00', 'C', '9' )
)

我们正在对每个 id 进行聚合，我们想使用 COUNT_IF查看有多少行符合条件，如果是 >0 我们很高兴

SELECT 
    id, 
    count_if(pipeline_id='A')>0 AS hit_stage_a,
    count_if(pipeline_id='B')>0 AS hit_stage_b,
    count_if(pipeline_id='C')>0 AS hit_stage_c,
    count_if(stage_id='4')>0 AS hit_stage_4,
    count_if(stage_id='5')>0 AS hit_stage_5,
    count_if(stage_id='6')>0 AS hit_stage_6
FROM fake_data
GROUP BY 1
ORDER BY 1;

给出:

<表类="s-表"><头>身份证HIT_STAGE_AHIT_STAGE_BHIT_STAGE_CHIT_STAGE_4HIT_STAGE_5HIT_STAGE_6<正文>1111正确正确错误错误正确错误2222正确正确错误错误正确正确3333正确错误正确错误错误错误