如何编写SQL查询为季度内日期分配连续天数编号并按日汇总营收

你当前的查询是按季度聚合统计，要实现按日展示每个日期在所属季度的连续天数编号及当日营收，需要调整分组逻辑并添加天数编号的计算逻辑，以下是适配主流数据库的修正方案：

PostgreSQL 版本 #

SELECT
  effective_date AS date,
  DATE_TRUNC('quarter', effective_date) AS quarter_start,
  DATE_PART('day', effective_date - DATE_TRUNC('quarter', effective_date)) + 1 AS quarter_day_number,
  SUM(Revenue) AS daily_revenue
FROM
  "Table Name"
WHERE
  effective_date BETWEEN '2022-10-01' AND '2023-03-31'
GROUP BY
  effective_date,
  DATE_TRUNC('quarter', effective_date)
ORDER BY
  effective_date ASC;

MySQL 8.0+ 版本 #

SELECT
  effective_date AS date,
  DATE_TRUNC('quarter', effective_date) AS quarter_start,
  DATEDIFF(effective_date, DATE_TRUNC('quarter', effective_date)) + 1 AS quarter_day_number,
  SUM(Revenue) AS daily_revenue
FROM
  `Table Name`
WHERE
  effective_date BETWEEN '2022-10-01' AND '2023-03-31'
GROUP BY
  effective_date,
  DATE_TRUNC('quarter', effective_date)
ORDER BY
  effective_date ASC;

SQL Server 版本 #

SELECT
  effective_date AS date,
  DATEADD(qq, DATEDIFF(qq, 0, effective_date), 0) AS quarter_start,
  DATEDIFF(day, DATEADD(qq, DATEDIFF(qq, 0, effective_date), 0), effective_date) + 1 AS quarter_day_number,
  SUM(Revenue) AS daily_revenue
FROM
  [Table Name]
WHERE
  effective_date BETWEEN '2022-10-01' AND '2023-03-31'
GROUP BY
  effective_date,
  DATEADD(qq, DATEDIFF(qq, 0, effective_date), 0)
ORDER BY
  effective_date ASC;

关键逻辑说明 #

• 分组调整：从按季度分组改为按effective_date（日期）和quarter_start（季度起始日）分组，确保每个日期单独生成一行结果
• 天数编号计算：通过当前日期与季度起始日的天数差加1，实现季度第一天为第1天、后续日期连续编号的效果
• 营收汇总：用SUM(Revenue)统计当日总营收，若你的表中每个日期仅一条营收记录，也可直接使用Revenue字段

补充：显示季度内所有日期（含无营收日期） #

如果需要展示季度内的所有日期（即使该日期无营收记录，营收显示为0），可以通过生成日期序列再左连接的方式实现，以PostgreSQL为例：

WITH quarter_dates AS (
  SELECT generate_series(
    DATE_TRUNC('quarter', '2022-10-01'::date),
    DATE_TRUNC('quarter', '2023-03-31'::date) + INTERVAL '3 months' - INTERVAL '1 day',
    INTERVAL '1 day'
  )::date AS date
)
SELECT
  q.date,
  DATE_TRUNC('quarter', q.date) AS quarter_start,
  DATE_PART('day', q.date - DATE_TRUNC('quarter', q.date)) + 1 AS quarter_day_number,
  COALESCE(t.daily_revenue, 0) AS daily_revenue
FROM
  quarter_dates q
LEFT JOIN (
  SELECT
    effective_date,
    SUM(Revenue) AS daily_revenue
  FROM
    "Table Name"
  WHERE
    effective_date BETWEEN '2022-10-01' AND '2023-03-31'
  GROUP BY
    effective_date
) t ON q.date = t.effective_date
ORDER BY
  q.date ASC;

内容的提问来源于stack exchange，提问作者connermcnally