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:
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;
source to share
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.
source to share