主页 / dba / 问题 / 205752
Accepted
Gajus
Asked: 2018-05-04 13:12:05 +0800 CST

选择按时间间隔分组的数据的正确方法是什么？

  • 772

我需要显示一张图表，说明在不同日期的不同时间间隔发生了多少门票销售，例如，周五演出有多少门票提前 3 小时售出，等等。

最终结果将类似于：

在此处输入图像描述

我通过计算SELECT语句中不同偏移量的值编写了一个查询：

WITH
  -- Create a date range; use LEFT JOIN/ COALESCE to construct date specific report; we don't want gaps in the data
  dates AS (
    SELECT generate_series(
      '2018-04-26',
      '2018-04-28',
      INTERVAL '1 day'
    ) AS date
  ),
  sales AS (
    SELECT
      ts1.date,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at < INTERVAL '0 hour' THEN 1 ELSE 0 END) after_event_start,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '1 hour' AND INTERVAL '2 hour' THEN 1 ELSE 0 END) hour_0_to_hour_1,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '1 hour' AND INTERVAL '2 hour' THEN 1 ELSE 0 END) hour_1_to_hour_2,
    -- [..]
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '22 hour' AND INTERVAL '23 hour' THEN 1 ELSE 0 END) hour_22_to_hour_23,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '23 hour' AND INTERVAL '24 hour' THEN 1 ELSE 0 END) hour_23_to_hour_24,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at > INTERVAL '24 hour' THEN 1 ELSE 0 END) after_24_hour
    FROM ticket_sale ts1
    WHERE
      ts1.movie_id = 1012718 AND
      ts1.starts_at > '2018-04-26' AND
      ts1.starts_at < '2018-04-28'::date + INTERVAL '1 day'
    GROUP BY ts1.date
  )
SELECT
  to_char(d1.date, 'YYYY-MM-DD') "date",
  COALESCE(s1.hour_0_to_hour_1, 0) hour_0_to_hour_1,
  COALESCE(s1.hour_1_to_hour_2, 0) hour_1_to_hour_2,
  -- [..]
  COALESCE(s1.hour_22_to_hour_23, 0) hour_22_to_hour_23,
  COALESCE(s1.hour_23_to_hour_24, 0) hour_23_to_hour_24,
  COALESCE(s1.day_6_to_day_7, 0) after_24_hour
FROM dates d1
LEFT JOIN sales s1 ON s1.date = to_char(d1.date, 'YYYY-MM-DD')
ORDER BY d1.date

这行得通。但是，作为查询，它看起来很奇怪。

有没有更好的方法来计算不同时间间隔的销售额？

postgresql date

1 个回答

  1. Best Answer

    我真的把这个复杂化了。

    正如@Gaius 所建议的，我需要做的就是查询所有按购买时间分组的数据，即

    SELECT
  ts1.date,
  ROUND(EXTRACT(EPOCH FROM (ts1.starts_at - ts1.created_at))/3600) "hour",
  COUNT(*) sales_count
FROM ticket_sale ts1
WHERE
  ts1.movie_id = 1012718 AND
  ts1.date = '2018-04-26'
GROUP BY ts1.date, hour

    我可以在客户端对数据进行后处理，以将其分组为所需的间隔。

    • 1

相关问题