sql - 按条件对分区进行计数

我在PostGre数据库上有一张桌子：CREATE TABLE testAAA(Id integer PRIMARY KEY, datum date, COLA text, COLB text, COLC text, COLD int);/* Create few records in this table */INSERT INTO testAAA VALUES(1,to_date('01/01/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','1');INSERT INTO testAAA VALUES(2,to_date('01/02/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','1');INSERT INTO testAAA VALUES(3,to_date('01/03/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','1');INSERT INTO testAAA VALUES(4,null,'PLANTA','VENDORA','OPA','1');INSERT INTO testAAA VALUES(5,to_date('01/10/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','5');INSERT INTO testAAA VALUES(6,to_date('01/10/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','10');INSERT INTO testAAA VALUES(7,to_date('01/11/2018','dd/mm/yyyy'),'PLANTA','VENDORB','OPA','50');INSERT INTO testAAA VALUES(8,to_date('01/10/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPA','10');INSERT INTO testAAA VALUES(9,to_date('01/11/2018','dd/mm/yyyy'),'PLANTA','VENDORA','OPB','5');COMMIT;我确实有这样的疑问：SELECT COLA,COLB,COLC,to_char(datum,'YYYY-MM') AS datum_c, SUM(COLD) FILTER (WHERE (datum >= now() - interval '6 month') AND (datum <= now())) OVER (PARTITION BY COLA, COLB, COLC) / 6.0 AS AVG_6_month, /* TO Aggregate at month level we need to PARTITION IT */ SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA,to_char(datum,'YYYY-MM')) AS TOT_ORDERED, SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA,COLB,to_char(datum,'YYYY-MM')) AS TOT_ORDERED_PLANT, (SUM(COLD) FILTER (WHERE datum >= now() - interval '6 month') OVER (PARTITION BY COLA, COLB, COLC) / 6.0) / (SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA))*100 AS Variation, CASE WHEN (SUM(COLD) FILTER (WHERE datum >= now() - interval '6 month') OVER (PARTITION BY COLA, COLB, COLC) / 6.0) / (SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA))*100 >= 10 THEN 'A' WHEN (SUM(COLD) FILTER (WHERE datum >= now() - interval '6 month') OVER (PARTITION BY COLA, COLB, COLC) / 6.0) / (SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA))*100 < 10 THEN 'B' END AS var_typeFROM testAAA;我想对分公司可乐/COLB的TOT数进行变异我试图在case语句中添加count：WHEN (SUM(COLD) FILTER (WHERE datum >= now() - interval '6 month') OVER (PARTITION BY COLA, COLB, COLC) / 6.0) / (SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA))*100 >= 10 THEN count((SUM(COLD) FILTER (WHERE datum >= now() - interval '6 month') OVER (PARTITION BY COLA, COLB, COLC) / 6.0) / (SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA))*100)但我又犯了个错误：42803:聚合函数调用不能包含窗口函数调用。######在“刺穿者”提示后编辑withx as ( -- first, compute the window functions select COLA,COLB,COLC,to_char(datum,'YYYY-MM') AS datum_c, SUM(COLD) FILTER (WHERE (datum >= now() - interval '6 month') AND (datum <= now())) OVER (PARTITION BY COLA, COLB, COLC) / 6.0 AS AVG_6_month, /* TO Aggregate at month level we need to PARTITION IT */ SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA,to_char(datum,'YYYY-MM')) AS TOT_ORDERED, SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA,COLB,to_char(datum,'YYYY-MM')) AS TOT_ORDERED_PLANT, (SUM(COLD) FILTER (WHERE datum >= now() - interval '6 month') OVER (PARTITION BY COLA, COLB, COLC) / 6.0) / (SUM(COLD) FILTER (WHERE datum is not null) OVER (PARTITION BY COLA))*100 AS Variation from testAAA)select*, COUNT(Variation) FILTER (WHERE Variation < 10) OVER (PARTITION BY COLA, COLB) AS below10, COUNT(Variation) FILTER (WHERE Variation > 10) OVER (PARTITION BY COLA, COLB) AS above10from x 最佳答案 这不是一个答案，而是一个不适合评论部分的评论。当您有多个窗口函数，并且您希望操作它们的结果（如本例所示）时，我倾向于在CTE（公共表表达式）中计算它们；然后，我将它们用作主查询中的简单标量值。这让我的生活轻松多了。例如，这样的查询：select sum(a) over(partition by region_id order by created_at) / sum(b) over(partition by region_id order by deleted_at) as scorefrom my_table我会将查询改写为a）预先计算“复杂”值，然后2）在简单查询中使用它们，如：withx as ( -- first, compute the window functions select sum(a) over(partition by region_id order by created_at) as sum_a sum(b) over(partition by region_id order by deleted_at) as sum_b from my_table)select sum_a, sum_b, sum_a / sum_b, -- then use them for any purposes sum_a * sum_bfrom x也许您可以使用此策略使查询看起来更简单，以便调试更容易。关于sql - 按条件对分区进行计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53836772/