在PostgreSQL中返回按月计算的数量的查询

Question

我正在尝试在PostgreSQL中编写查询，该查询应在给定两个日期时按“月份”返回结果。

  SELECT count(overall_sl) as total_sales, count(CASE WHEN overall_sl < value_1 THEN 1 END) failed_sales
    FROM  (
                SELECT overall_sl, value_1
                FROM   salecombined c where c.date_updated between '2018-01-01' and '2018-12-31'
                 GROUP  BY dept_name, date_updated, date, overall_sl, no_addons,  
                value_1, category_id, subcategory_id, branch_name
     ) sales; 

我希望结果是这样的（上面的查询并没有实现这个功能）。

start_dt    end_dt       total_sales   failed_sales
--------    ------       -----------   -------------
2018-01-01  2018-01-31   0              0
2018-02-01  2018-02-28   589            154
2018-03-01  2018-03-31   107            14
2018-04-01  2018-04-30   375            114
-- and so on

我写了下面的查询语句，但执行时间较长；请问如何优化？

SELECT  date_trunc('month', dd):: date start_dt,
(date_trunc('month', dd::DATE) + interval '1 month' - interval '1 day')::date as end_dt, 

    (select count(overall_sl) from (
            SELECT overall_sl
        FROM   salecombined c                
        WHERE c.date_updated between date_trunc('month', dd):: date and (date_trunc('month', dd::DATE) + interval '1 month' - interval '1 day')::date
        GROUP  BY dept_name, date_updated, date, overall_sl, no_addons,  
        category_id, subcategory_id, branch_name
         ) jobs
     ) total_sales,

     (select count(CASE WHEN overall_sl < value_1 THEN 1 END) from (
            SELECT overall_sl, value_1
        FROM   salecombined c                
        WHERE c.date_updated between date_trunc('month', dd):: date and (date_trunc('month', dd::DATE) + interval '1 month' - interval '1 day')::date
        GROUP  BY dept_name, date_updated, date, overall_sl, no_addons,  
        value_1, category_id, subcategory_id, branch_name
         ) jobs
     ) failed_sales

FROM generate_series
    ( '2018-01-01'::timestamp 
    , '2018-12-11'::timestamp
    , '1 month'::interval) dd

我不是SQL或PostgreSQL的专家。

- Showkath

---

请添加一些示例数据和您的表结构。请稍微解释一下您的用例，它还不是很清楚。什么是“2个值”？ “按月份”是什么意思？ - S-Man

我所说的按月份计算是指，如果您选择日期范围（例如-1年），它应该返回该年每个月的计数（12行，对应12个月）。 - Showkath

Answer 1

我不太确定，但也许你正在搜索GROUP BY date_trunc():

演示：db<>fiddle

SELECT 
    date_trunc('month', sdate)::date as month_begin,
    (date_trunc('month', sdate) + interval '1 month -1 day')::date as month_end,
    SUM(value)
FROM sales    
WHERE sdate BETWEEN <start> and <end>
GROUP BY date_trunc('month', sdate)

1. date_trunc('month', date) 将日期转换为该月的第一天。因此，您可以将所有在一个月内的日期分组（因为该组中的所有日期都是该月的第一天）
2. date_trunc('month', sdate) + interval '1 month -1 day' 计算一个月的最后一天（date_trunc 转到第一天，添加一个月以进入下个月的第一天，减去一天以到达下个月的前一天=当前月的最后一天。）

- S-Man

---

非常感谢 @S-Man -- 你太棒了！！它起作用了，查询速度提高了50%以上 :) - Showkath