sql - 如何根据不同条件在一次查询中对单个字段进行两次计数

Question

我想对同一个字段进行两次计数，根据来自另一个子查询的不同条件进行区分。伪代码是这样的:

select countd(svc_ord_nbr) <if start_dt = end_dt> as fixed, 
       countd(svc_ord_nbr) <else> as unfixed
       from [subquery]

子查询基本上是根据相同的 svc_ord_nbr 获取事件日期的最小值和最大值

select svc_ord_nbr, date_part('day', min(e.START_DTM)) as start_dt,
    date_part('day', max(e.START_DTM)) as end_dt
    from order o join activity a on o.svc_activity_id = a.activity_id
    group by svc_ord_nbr

How to count same field twice based on a boolean中的答案建议在 bool 值上执行 case when。但是，它没有考虑不同的值。有没有办法只对相距很远的值对同一个字段进行两次计数。

Answer 1

您可以使用条件聚合:

select 
  count(DISTINCT CASE WHEN start_dt = end_dt THEN svc_ord_nbr END) as fixed, 
  count(DISTINCT CASE WHEN start_dt <> end_dt THEN svc_ord_nbr END )as unfixed
from [subquery];

---

或者使用窗口函数:

select DISTINCT
  count(DISTINCT svc_ord_nbr) FILTER(WHERE start_dt = end_dt) OVER() as fixed, 
  count(DISTINCT svc_ord_nbr) FILTER(WHERE start_dt <> end_dt) OVER() as unfixed
from [subquery];

如果需要，您可以在 OVER 中添加 PARTITION BY。