SQL-如何在一年的每个日期按年龄和状态对项目进行分组/计数? [英] SQL - How to group/count items by age and status on every date of a year?

问题描述

我正在尝试从支持凭单的多年数据集(tickets表)构建查询，并为每张凭单的相关列分别为ticked_id，status，created_on日期和closed_on日期.还有一个通用的dates表，我可以将其联接/查询到日期列表.

I am trying to build a query from multi-year data set (tickets table) of support tickets, with relevant columns of ticked_id, status, created_on date and closed_on date for each ticket. There is also a generic dates table I can join/query to a list of dates.

我想为今年创建一个燃尽"图表，以显示在今年任何给定日期至少有一年历史的公开门票数量.我已经能够创建使用sum(case...语句按日期分组的表-例如，显示在给定的一周内创建了多少张票证-但我不知道如何在此期间的每一天或每一周进行分组当天且至少有一年历史的车票数量.

I'd like to create a "burn down" chart for this year that displays the number of open tickets that were at least a year old on any given date this year. I have been able to create tables that use a sum(case... statement to group by a date - for example to show how many tickets were created on a given week - but I can't figure out how to group by every day or week this year the number of tickets that were open on that day and at least a year old.

感谢您的帮助.

示例数据:

ticket_id | status | created_on | closed_on
--------------------------------------------
 1          open     1/5/2019     
 2          open     1/26/2019
 3          closed   1/28/2019    2/1/2020
 4          open     6/1/2019
 5          closed   6/5/2019     1/1/2020

我寻求的示例结果:

Date (2020)    |     Count of Year+ Aged Tickets
------------------------------------------------
1/1/2020             0
1/2/2020             0
1/3/2020             0
1/4/2020             0
1/5/2020             1
1/6/2020             1
... (skipping dates here but want all dates in results)...
1/25/2020            1
1/26/2020            2
1/27/2020            2
1/28/2020            3
1/29/2020            3
1/30/2020            3
1/31/2020            3
2/1/2020             2
... (skipping dates here but want all dates up to current date in results)...

• ticket_id 1已于2020年1月5日满一年，并且仍在营业 (仍在计数中)
• ticket_id 2于2020年1月6日达到1岁，并且仍处于开放状态(仍在计数中)
• ticket_id 3在2020年1月28日达到了一年的年龄，并且仍处于开放状态，从而增加了计数，但在2020年2月1日关闭，从而减少了计数.
如果
• ticket_id 4仅在2020年6月1日仍处于打开状态，则不会添加到计数中，而在此之前关闭时则不会添加
• ticket_id 5永远不会出现在计数中，因为它从未达到1岁并且已关闭
• ticket_id 1 reached one year of age on 1/5/2020 and is still open (remains in count)
• ticket_id 2 reached one year of age on 1/26/2020 and is still open (remains in count)
• ticket_id 3 reached one year of age on 1/28/2020 and was still open, adding to the count, but was closed on 2/1/2020, reducing the count
• ticket_id 4 will only add to the count if it is still open on 6/1/2020, but not if it is closed before then
• ticket_id 5 will never appear in the count because it never reached one year of age and is closed

推荐答案

一个选项是构建一个顺序的日期列表，然后使用左连接"和条件逻辑来创建表格，最后进行汇总.

One option is to build a sequential list of dates, then bring the table with a ‘left join` and conditional logic, and finally aggregate.

这将为您提供2020年想要的结果.

This would give the results you want for year 2020.

select d.dt, count(t.ticket_id) no_tickets
from (
    select date '2020-01-01' + I * interval '1 day' dt 
    from generate_series(0, 365) i 
) d
left join mytable t
    on t.created_on + interval '1 year' <= d.dt 
    and (
        t.closed_on is null 
        or t.closed_on > d.dt
    )
group by d.dt

如果您的Redshift版本不支持generate_series()，则可以将其模拟为自定义数字表，或者使用row_number()来模拟大型表(例如mylargetable):

If your version of Redshift does not support generate_series(), you can emulate it a custom number table, or with row_number() against a large table (say mylargetable):

select d.dt, count(t.ticket_id) no_tickets
from (
    select date '2020-01-01' + row_number() over(order by 1) * interval '1 day' dt 
    from mylargetable 
) d
left join mytable t
    on t.created_on + interval '1 year' <= d.dt 
    and (
        t.closed_on is null 
        or t.closed_on > d.dt
    )
where d.dt < date '2021-01-01'
group by d.dt

这篇关于SQL-如何在一年的每个日期按年龄和状态对项目进行分组/计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！