sql - 有效/简单地计算同一数据集的不同范围

Question

我正在 Postgresql 中编写一个函数，它将返回一些针对特定时区(输入)计算的指标。

示例结果:

主要问题是这只是一个指标。我需要从其他表中获取其他 9 个指标。

对于实现此目标的更简洁的方法有什么建议吗？

    CREATE OR REPLACE FUNCTION dashboard_metrics(destination_timezone TEXT)
  RETURNS TABLE(
  metric TEXT,
  count BIGINT
  ) AS
  $func$
  DECLARE
    today        TIMESTAMP;
    tomorrow     TIMESTAMP;
    yesterday    TIMESTAMP;
    ereyesterday TIMESTAMP;
    last7days    TIMESTAMP;
    last30days   TIMESTAMP;
    last60days   TIMESTAMP;
  BEGIN
    SELECT 'today' AT TIME ZONE destination_timezone
    INTO today;
    SELECT ('tomorrow' AT TIME ZONE destination_timezone)
    INTO tomorrow;
    SELECT ('yesterday' AT TIME ZONE destination_timezone)
    INTO yesterday;
    SELECT ('yesterday' AT TIME ZONE destination_timezone) - INTERVAL '1 day'
    INTO ereyesterday;
    SELECT ('today' AT TIME ZONE destination_timezone) - INTERVAL '7 days'
    INTO last7days;
    SELECT ('today' AT TIME ZONE destination_timezone) - INTERVAL '30 days'
    INTO last30days;
    SELECT ('today' AT TIME ZONE destination_timezone) - INTERVAL '60 days'
    INTO last60days;
    RETURN QUERY
--TODAY
    (SELECT
       'ideastoday' :: TEXT AS metric,
       COUNT(1)             AS count
     FROM analytics_ideas
     WHERE created_on >= today AND created_on < tomorrow AND analytics_ideas.space_id = 1)
    UNION ALL
(SELECT
       'ideasyesterday' :: TEXT AS metric,
       COUNT(1)             AS count
     FROM analytics_ideas
     WHERE created_on >= yesterday AND created_on < today AND analytics_ideas.space_id = 1)
    UNION ALL
      (SELECT
       'ideasereyesterday' :: TEXT AS metric,
       COUNT(1)             AS count
     FROM analytics_ideas
     WHERE created_on >= ereyesterday AND created_on < yesterday AND analytics_ideas.space_id = 1)
    UNION ALL
      (SELECT
       'ideaslast7days' :: TEXT AS metric,
       COUNT(1)             AS count
     FROM analytics_ideas
     WHERE created_on >= last7days AND created_on < today AND analytics_ideas.space_id = 1)
    UNION ALL
      (SELECT
       'ideaslast30days' :: TEXT AS metric,
       COUNT(1)             AS count
     FROM analytics_ideas
     WHERE created_on >= last30days AND created_on < today AND analytics_ideas.space_id = 1)
    UNION ALL
      (SELECT
       'ideaslast60days' :: TEXT AS metric,
       COUNT(1)             AS count
     FROM analytics_ideas
     WHERE created_on >= last60days AND created_on < today AND analytics_ideas.space_id = 1);
  END
  $func$ LANGUAGE plpgsql;

Answer 1

您提到的其他指标的细节可能会使事情复杂化，但您提供的功能可以大大简化。

在高层次上，这通常归结为:如果您希望您的指标共享逻辑，您需要使用数据来区分它们。您需要一个通用的数据结构来封装将一个指标与另一个指标区分开来的所有内容。这样，您就可以编写通用算法来处理这些结构。

CREATE OR REPLACE FUNCTION dashboard_metrics(destination_timezone TEXT)
  RETURNS TABLE(
    Metric TEXT,
    Count BIGINT
  ) AS
$func$
  WITH MetricDef (Metric, StartDay, EndDay) AS (
    VALUES
      ('ideastoday',         0,  1),
      ('ideasyesterday',    -1,  0),
      ('ideasereyesterday', -2, -1),
      ('ideaslast7days',    -7,  0),
      ('ideaslast30days',  -30,  0),
      ('ideaslast60days',  -60,  0)
  )
  SELECT
    MetricDef.Metric,
    COUNT(*) AS Count
  FROM
    MetricDef,
    analytics_ideas
  WHERE
    created_on >= ('today' AT TIME ZONE destination_timezone) + MetricDef.StartDay * INTERVAL '1 day' AND
    created_on < ('today' AT TIME ZONE destination_timezone) + MetricDef.EndDay * INTERVAL '1 day' AND
    analytics_ideas.space_id = 1
  GROUP BY
    MetricDef.Metric
$func$
LANGUAGE SQL STABLE;

请注意，这不会对输出施加任何排序。如果这很重要，您应该在 MetricDef 中包含一个 DisplayOrder 字段，并在主查询的某处添加一个 ORDER BY。