sql-如何在一年中的每个日期按年龄和状态对项目进行分组/计数-第2部分

ygya80vv 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(166)

这个问题类似于我在这里提出并回答的另一个问题：sql-如何在一年中的每个日期按年龄和状态对项目进行分组/计数？但我搞不懂这个新问题。我需要一个查询来帮助将下面示例表中的数据分组到下面所需的结果中。目标是在每个连续日期结束时，按组计算给定状态下的票证总数，从指定日期开始，到当前日期结束。
示例数据表（票证）：

ticket_id | opened    | assigned  | in_work  | closed     | assigned_group
---------------------------------------------------------------------------
 1          1/1/2020    1/2/2020    1/5/2020   1/5/2020     Network
 2          1/2/2020    1/3/2020    1/3/2020   1/5/2020     Software
 3          1/2/2020    1/5/2020                            Hardware
 4          1/2/2020                                        Network
 5          1/3/2020    1/4/2020    1/5/2020                Software
 6          1/3/2020                                        Network
... and more continuing in similar pattern

期望结果：

Date      | assigned_group | num_open | num_assigned | num_in_work | num_closed |
---------------------------------------------------------------------------
 1/1/2020   Network          1            0              0             0 
 1/1/2020   Software         0            0              0             0 
 1/1/2020   Hardware         0            0              0             0 
 1/2/2020   Network          1            1              0             0 
 1/2/2020   Software         1            0              0             0 
 1/2/2020   Hardware         1            0              0             0 
 1/3/2020   Network          2            1              0             0 
 1/3/2020   Software         1            0              1             0 
 1/3/2020   Hardware         1            0              0             0  
 1/4/2020   Network          2            1              0             0 
 1/4/2020   Software         0            1              1             0 
 1/4/2020   Hardware         1            0              0             0 
 1/5/2020   Network          2            0              0             1 
 1/5/2020   Software         0            0              1             1 
 1/5/2020   Hardware         0            1              0             0  
... continuing to present date

谢谢您！

sql amazon-redshift recursive-query

来源：https://stackoverflow.com/questions/62823501/sql-how-to-group-count-items-by-age-and-status-on-every-date-of-a-year-part

1条答案

按热度按时间

q5lcpyga1#

您可以取消PIVOT并使用累计和：

with da as (
      select opened as date, assigned_group, 1 as open_inc, 0 as assigned_inc, 0 as in_work_inc, 0 as closed_inc
      from t
      union all
      select assigned, assigned_group, -1, 1, 0, 0
      from t
      union all
      select in_work, assigned_group, 0 -1, 1, 0
      from t
      union all
      select closed, assigned_group, 0, 0, -1, 1
      from t
     )
select date, assigned_group,
       sum(sum(open_inc)) over (partition by assigned_group order by date) as num_opens,
       sum(sum(assigned_inc)) over (partition by assigned_group order by date) as num_assigned,
       sum(sum(in_work_inc)) over (partition by assigned_group order by date) as num_in_work,
       sum(sum(closed_inc)) over (partition by assigned_group order by date) as num_closed
from da
group by date, assigned_group
order by date, assigned_group;

赞(0）回复(0）举报 2021-07-24

我来回答

sql-如何在一年中的每个日期按年龄和状态对项目进行分组/计数-第2部分

1条答案

相关问题

热门标签

最新问答