0

I am unioning multiple tables together in Postgres, where the timestamp is slightly different, but the same if I round to the hour. Is it possible to make each union show on the same row, if the date is the same? Currently I'm getting duplicate rows of each date.

select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp), 
COALESCE(count(*), 0) as "1489",
null as "1616"
from actions
where group_id = 1489
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
union all
select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp), 
null as "1489",
COALESCE(count(*), 0) as "1616"
from actions
where group_id = 1616
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
order by 1

It returns:

enter image description here

  • I see potential time zone problems in your query. Are you operating with UTC? And there may be a faster solution, yet. But there is no table definition and not even your version of Postgres. Minimum requirements ... And please always post data as text not as image. – Erwin Brandstetter Jun 2 '17 at 15:33
1

You can use FULL JOIN like this:

SELECT
    COALESCE(a1.t, a2.t) as t,
    a1."1489",
    a2."1616"
FROM
(
    select 
        date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t, 
        COALESCE(count(*), 0) as "1489"
    from actions
    where group_id = 1489
        and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
    group by 1
) as a1
FULL JOIN
(
    select 
        date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t, 
        COALESCE(count(*), 0) as "1616"
    from actions
    where group_id = 1616
        and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
    group by 1
) as a2 ON (a1.t=a2.t)
ORDER BY 1;

Or just use CASE:

SELECT
    date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
    SUM(CASE WHEN group_id=1489 THEN 1 ELSE 0 END) as "1489",
    SUM(CASE WHEN group_id=1616 THEN 1 ELSE 0 END) as "1616"
FROM actions
WHERE 
    group_id in (1489, 1616)
    AND TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
GROUP BY 1
ORDER BY 1;
0

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, "1489" int, "1616" 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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.