Reading different ranges from the same dataset efficiently / easily

Question

Reading different ranges from the same dataset efficiently / easily

I am writing a function in Postgresql that will return some metrics calculated for a specific timezone (input).

Result example:

enter image description here

The main problem is that this is only one metric. I need to get another 9 records from other tables.

Any suggestions for a less verbose way to achieve this?

    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;

+3

sql postgresql

Carlos mendes 08 oct. 14 at 17:25

source to share

1 answer

Nick barnes · Accepted Answer · 2014-10-10T02:53:57+0000

The specifics of the other metrics you mentioned might complicate things, but the function you provided might be simplified quite a bit.

At a high level, this is what it usually boils down to: if you want your metrics to share logic , you need to differentiate them from the data . You need a common data structure to encapsulate everything that distinguishes one metric from another. That being said, you can write a generalized algorithm to handle these structures.

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;

Please note that this will not impose any exit orders. If important, you should include the field DisplayOrder

in MetricDef

and add ORDER BY

to the main query.

Reading different ranges from the same dataset efficiently / easily

More articles: