如何在Snowflake中执行Pivot(Count(*))功能

Question

我的查询如下：

我得到一个错误“意外的'*'”。在第6行，请让我知道，如何重写或更正这个问题

select * from (SELECT gl gl_acc,
                               reg reg,
                               mapp map_name,
                               field as f1
                          FROM gl_acc a
                         WHERE TYPE = 'AGG') PIVOT (count(*)
                                             FOR f1
                                             IN  (
                                                 'JOURN' ,
                                                 'JOU' ,
                                                 'J' ,
                                                 'CASE_' ,
                                                 'JOUR' 
                                                ))as p;

谢谢，去吧

Answer 1

只需使用条件聚合：

SELECT gl, reg, mapp,
       SUM(CASE WHEN field = 'JOURN' THEN 1 ELSE 0 END) as journ,
       SUM(CASE WHEN field = 'JOU' THEN 1 ELSE 0 END) as jou,
       SUM(CASE WHEN field = 'J' THEN 1 ELSE 0 END) as j,
       SUM(CASE WHEN field = 'CASE_' THEN 1 ELSE 0 END) as case_,
       SUM(CASE WHEN field = 'JOUR' THEN 1 ELSE 0 END) as jour       
FROM gl_acc a
WHERE TYPE = 'AGG'
GROUP BY gl, reg, mapp;

或者更简单地使用count_if()

SELECT gl, reg, mapp,
       COUNT_IF(field = 'JOURN') as journ,
       COUNT_IF(field = 'JOU') as jou,
       COUNT_IF(field = 'J') as j,
       COUNT_IF(field = 'CASE_') as case_,
       COUNT_IF(field = 'JOUR') as jour       
FROM gl_acc a
WHERE TYPE = 'AGG'
GROUP BY gl, reg, mapp;