如何只按一列进行分组？

Question

我想只选择一个列(Failed_operation)和distinct列(SN)，如下代码所示，但我收到错误:列"rw_pcba.sn“必须出现在GROUP BY子句中或用于聚合函数中

我尝试在( SN )上删除distinct，然后结果是出现了，但结果也包含重复的SN。我不想在结果中出现重复的序列号。

SELECT DISTINCT ON (sn) Failed_operation
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) > 0
                AND (extract(day FROM NOW() - fail_timestamp)) <= 15 THEN 1 ELSE NULL END) AS AgingLessThan15
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) > 15
                AND (extract(day FROM NOW() - fail_timestamp)) <= 30 THEN 1 ELSE NULL END) AS Aging16To30
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) > 30
                AND (extract(day FROM NOW() - fail_timestamp)) <= 60 THEN 1 ELSE NULL END) AS Aging31To60
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) > 60 THEN 1 ELSE NULL END) AS AgingGreaterThan60
    ,count(CASE WHEN (extract(day FROM NOW() - fail_timestamp)) <= 0 THEN 1 ELSE NULL END) AS Aging0
FROM rw_pcba
WHERE rework_status = 'In-Process'
GROUP BY Failed_operation
ORDER BY sn
    ,Failed_operation ASC

Answer 1

您需要使用列sn进行分组，当您使用group by时，它将是sn和failed_operation的不同组合，您不必指定distinct。

SELECT sn, Failed_operation,
count  (case when (extract(day from NOW() - fail_timestamp)) >0 and (extract(day from NOW() - fail_timestamp))<=15 then 1 else null end) as AgingLessThan15,
count  (case when (extract(day from NOW() - fail_timestamp)) >15 and (extract(day from NOW() - fail_timestamp))<=30 then 1 else null end) as Aging16To30,
count  (case when (extract(day from NOW() - fail_timestamp)) >30 and (extract(day from NOW() - fail_timestamp))<=60 then 1 else null end) as Aging31To60,
count  (case when (extract(day from NOW() - fail_timestamp)) >60 then 1 else null end) as AgingGreaterThan60,
count  (case when (extract(day from NOW() - fail_timestamp)) <=0 then 1 else null end) as Aging0
FROM rw_pcba where rework_status='In-Process' 
GROUP by sn,Failed_operation  ORDER BY  sn,Failed_operation ASC

Answer 2

您希望既按failed_operation又按sn进行聚合。我还认为你可以简化每一列的计算：

SELECT sn, Failed_operation,
       count(*) filter (where fail_timestamp > current_date and fail_timestamp < current_date + interval '15 day') as AgingLessThan15,
       count(*) filter (where fail_timestamp > current_date + interval '15 day' and fail_timestamp < current_date + interval '30 day') as Aging16To30,
       count(*) filter (where fail_timestamp > current_date + interval '30 day' and fail_timestamp < current_date + interval '600 day') as Aging31To60,
       count(*) filter (where fail_timestamp > current_date + interval '60 day') as AgingGreaterThan60,
       count(*) filter (where fail_timestamp <= current_date) as Aging0
FROM rw_pcba
WHERE rework_status = 'In-Process'
GROUP BY sn, Failed_operation
ORDER BY sn, Failed_operation ASC;

对于这种类型的逻辑，我更喜欢直接进行日期比较，而不是处理日期之间的差异。我只是发现它更容易遵循。例如，使用current_date而不是now()消除了now()的时间组件会发生什么的问题。

编辑：

在旧版本的Postgres中，你可以使用sum来表达：

       sum( (fail_timestamp > current_date and fail_timestamp < current_date + interval '15 day')::int ) as AgingLessThan15,