3 update link
source | link

That's generally faster and can use an index on created. (Makes it "sargablesargable".)

That's generally faster and can use an index on created. (Makes it "sargable".)

That's generally faster and can use an index on created. (Makes it "sargable".)

2 deleted 2 characters in body
source | link
SELECT * FROM crosstab(
    $$SELECT date_trunc('hour', to_timestamp(created / 1000) AT TIME ZONE 'UTC')
           , group_id
           , count(*)::int
      FROM   actions
      WHERE  group_id IN (1489, 1616)  -- more?
      AND    created  >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000
      GROUP  BY 1, 2
      ORDER  BY 1, 2$$

   ,$$SELECT unnest('{1489, 1616}'::int[])$$
   ) AS ct (hour timestamp, "g1489""1489" int, "g1616""1616" int);  -- more?
SELECT * FROM crosstab(
    $$SELECT date_trunc('hour', to_timestamp(created / 1000) AT TIME ZONE 'UTC')
           , group_id
           , count(*)::int
      FROM   actions
      WHERE  group_id IN (1489, 1616)  -- more?
      AND    created  >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000
      GROUP  BY 1, 2
      ORDER  BY 1, 2$$

   ,$$SELECT unnest('{1489, 1616}'::int[])$$
   ) AS ct (hour timestamp, "g1489" int, "g1616" int);  -- more?
SELECT * FROM crosstab(
    $$SELECT date_trunc('hour', to_timestamp(created / 1000) AT TIME ZONE 'UTC')
           , group_id
           , count(*)::int
      FROM   actions
      WHERE  group_id IN (1489, 1616)  -- more?
      AND    created  >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000
      GROUP  BY 1, 2
      ORDER  BY 1, 2$$

   ,$$SELECT unnest('{1489, 1616}'::int[])$$
   ) AS ct (hour timestamp, "1489" int, "1616" int);  -- more?
1
source | link

Assumptions

  • Current Postgres 9.6.
  • actions.created is a bigint column holding a count of ms since 1970-01-01 UTC (epoch in microseconds).
  • actions.groupid is integer.

The basic query:

SELECT date_trunc('hour', to_timestamp(created / 1000) AT TIME ZONE 'UTC') AS hour  -- !
     , group_id
     , count(*)::int AS ct       -- count never returns NULL
FROM   actions
WHERE  group_id IN (1489, 1616)  -- more?
AND    created  >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000  -- !
GROUP  BY 1, 2
ORDER  BY 1, 2;

Major points

Don't make your query depend on the time zone setting of the session or you may be in for surprises: The result changes with the setting. Remember that "today" is a function of the time zone you are in. Detailed explanation:

Replace 'UTC' in above query with the time zone you want your times and dates to reflect. Use a time zone name (not an abbreviation) to be perfectly sure.

Transform the WHERE condition:

TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7

to the equivalent:

created >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000`

That's generally faster and can use an index on created. (Makes it "sargable".)

Depending on data distribution, size of the table and typical you may want a multicolumn index on (group_id, created DESC) to optimize read performance. Performance optimization depends on the complete picture, though ...

The aggregate function count() never returns NULL. COALESCE(count(*), 0) is useless noise and can safely be replaced with just count(*).

Pivot

You want the result pivoted with one column per selected group_id. Use crosstab() from the additional tablefunc module for fastest results. If you are not familiar with it, read basic instructions here first:

SELECT * FROM crosstab(
    $$SELECT date_trunc('hour', to_timestamp(created / 1000) AT TIME ZONE 'UTC')
           , group_id
           , count(*)::int
      FROM   actions
      WHERE  group_id IN (1489, 1616)  -- more?
      AND    created  >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000
      GROUP  BY 1, 2
      ORDER  BY 1, 2$$

   ,$$SELECT unnest('{1489, 1616}'::int[])$$
   ) AS ct (hour timestamp, "g1489" int, "g1616" int);  -- more?

Missing elements are NULL. You cannot catch this with COALESCE around count() in the inner query. Use COALESCE in the outer query instead. Like:

SELECT hour, COALESCE("1489", 0) AS "1489", COALESCE("1616", 0) AS "1616" FROM crosstab( ...

Note that hours without any rows are still missing in the result. Use an OUTER JOIN to a complete time grid if you want empty rows in the result. Like: