Pivot time series into date period columns with array aggregate cells

Question

Given a time series of events where each event has a successful or unsuccessful outcome, how do I pivot the ratio of events by entity and time period columns with an aggregate array cell value? I suspect this can be done with crosstab query and array_agg.

Akin to uptime status reports, I'm trying to calculate something like this in SQL:

The volume of data is small enough that I could do the reduction client-side in a general purpose language, but it would be nice to do this efficiently in the database for a larger amount of data.

Time Series Example

+-----------------------------------------+
|  DateTime             Entity    Result  |
+-----------------------------------------+
| 2016-01-01 11:00...  :a        :success |
| 2016-01-01 17:00...  :a        :success |  -- two events for :a on same day
| 2016-01-01 11:01...  :b        :fail    |
| 2016-01-01 11:03...  :c        :success |
| 2016-01-01 13:00...  :d        :success |  -- only one event for :d
| 2016-01-02 11:00...  :a        :success |
| 2016-01-02 11:01...  :b        :fail    |
| 2016-01-02 11:03...  :c        :success |
| ...                                     |
+-----------------------------------------+

Desired Aggregate

Where each value cell after the key column is an array with shape [cnt_total cnt_success cnt_fail]:

+-----------------------------------------------+
| Entity     2016-01-01  2016-01-02  2016-01-xx |
+-----------------------------------------------+
| :a            [2 2 0]     [1 1 0]         ... |
| :b            [1 0 1]     [1 0 1]         ... |
| :c            [1 1 0]     [1 0 1]         ... |
| :d            [1 1 0]     [0 0 0]         ... |
+-----------------------------------------------+

To keep things simple, this report will never need more than 10 date window columns and I can dynamically template the SQL pivot output.

If I had to decompose this transformation:

1. Aggregate time series by date window (hour/day/week/month/quarter/year) and Result.
2. Accumulate the counted aggregate into some data structure like a hash-map or array of [count_total count_success count_fail]
3. Return the accumulated two-dimensional result as [entity period1 period2 ...] for displaying % on the client.

Answer 1

This question is old, but you haven’t accepted any answer yet, so I will add another one.

You need some aggregation of your data, and a pivot table. The most elegant way to do the former is by means of a CTE, and the most elegant way to do the latter is with CROSSTAB; however, as of Postgres 9.6, and unlike in other DBMS, you cannot reference a CTE from CROSSTAB. I will show an example for each of the two possible ways out: 1) Use a CTE, and reimplement a poor man’s pivoting on your own 2) Instead of a CTE, create a view once for all and reference it in your CROSSTAB queries. In both cases you’ll have to issue only one query per report, and you won’t have to create any temporary table.

The general problem of pivots is that, in pure SQL, you cannot define a query whose result has a variable number of columns, and you cannot define column headings dynamically. If you want that, you have to build your query with a procedural language, either server-side (plpgsql, as in Abelisto’s answer) or client-side (PHP, java, whatever...). My examples below are in pure SQL, so they have a fixed number of days (three, as in your example data), with fixed column headings ("day 1", "day 2", "day 3"), but they are built in a way that minimizes the needed edits when you change them.

First, the initial data. I started from the ones joanolo used, but my approach is different, because I use SMALLINT instead of BOOLEAN for result. My reason for doing this will become clear in the following.

CREATE TABLE time_series (
  date_time TIMESTAMP NOT NULL,
  entity TEXT NOT NULL,
  result SMALLINT DEFAULT 0 -- 1 means success, 0 failure.
);

INSERT INTO time_series VALUES
  ('2016-01-01 11:00', 'a', 1),
  ('2016-01-01 17:00', 'a', 1),
  ('2016-01-01 11:01', 'b', 0),
  ('2016-01-01 11:03', 'c', 1),
  ('2016-01-01 13:00', 'd', 1),
  ('2016-01-02 11:00', 'a', 1),
  ('2016-01-02 11:01', 'b', 0),
  ('2016-01-03 11:03', 'e', 1),
  ('2016-01-03 11:04', 'e', 1),
  ('2016-01-03 11:05', 'e', 1),
  ('2016-01-03 11:06', 'e', 0);

You only really need an array of two integers (a in my examples): a[1] (total count) and a[2] (success count). The failure count is simply a[1] - a[2] and the success percentage is 100*(a[2]::float)/a[1]. You can compute the total count by COUNT(result); if you define result SMALLINT you can simply use SUM(result) to keep track of the success count. If you store result as BOOLEAN, you have to use SUM(CASE WHEN result THEN 1 ELSE 0 END). If you store them as strings, SUM(CASE WHEN result = 'success' THEN 1 ELSE 0 END). If you cannot change your time_series table, edit the code below as appropriate.

Solution 1

This is inefficient ad rather ugly, but it’s worth being shown to illustrate how to use a CTE and what pains we had to suffer before CROSSTAB came along. When changing intervals, it has to be modified in four places: initial day, final day, rows in the main selection list, and rows in the list of joined tables. However, using a numeric column rn allows not to write explicit dates in the joined tables, which simplifies the task.

WITH ct AS (
  SELECT EXTRACT('days' FROM day - MIN(day) OVER()) + 1 AS rn, sub.* 
    FROM (
     SELECT 
          entity, 
          DATE_TRUNC('day', date_time) AS day, 
          ARRAY[COUNT(result), SUM(result)] AS a
       FROM time_series
      WHERE date_time BETWEEN TIMESTAMP '2016-01-01'                 -- initial day
                          AND TIMESTAMP '2016-01-03 23:59:59'        -- last second of final day
      GROUP BY 1,2
    ) AS sub
)
SELECT e.entity
       , d1.a AS "day 1"                                             -- add as many as you need
       , d2.a AS "day 2"
       , d3.a AS "day 3"
  FROM (SELECT DISTINCT entity FROM ct) e
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 1) d1 USING(entity) -- add as many as you need
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 2) d2 USING(entity)
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 3) d3 USING(entity)
  ORDER BY e.entity;


 entity | day 1 | day 2 | day 3 
--------+-------+-------+-------
 a      | {2,2} | {1,1} | 
 b      | {1,0} | {1,0} | 
 c      | {1,1} |       | 
 d      | {1,1} |       | 
 e      |       |       | {4,3}

Solution 2

The code for the view is essentially the CTE code of the previous example, but it is simpler, because CROSSTAB allows us using GENERATE_SERIES with timestamp values, so we don’t need a numeric rn column to categorize the data. Note that this view, once created, will not need modifications.

CREATE VIEW ts_view AS
  SELECT 
      entity,
      DATE_TRUNC('day', date_time) AS day, 
      ARRAY[COUNT(result), SUM(result)] AS a
    FROM time_series
    GROUP BY 1,2;

This is the main query. When changing intervals, it has to be modified in three places: initial day, final day, and output columns. Formatting is best done client-side, but in this case I have done it server-side. Instructions on how to change this are in the comments

SELECT * FROM CROSSTAB ($$
    SELECT 
        entity, 
        day,
        -- You have to repeat the result type of the following expression
        -- as the type of the "day N" columns below.
        -- e.g.  a --> INTEGER[] ,  100*(a[2]::FLOAT)/a[1] --> FLOAT , etc. 
        -- In TO_CHAR, D is changed to your locale's decimal point
        TO_CHAR(100*(a[2]::float)/a[1], '990D99')||'%' 
    FROM ts_view ORDER BY 1
  $$,$$
    SELECT GENERATE_SERIES (
      TIMESTAMP '2016-01-01',  -- initial day
      TIMESTAMP '2016-01-03',  -- final day
      '1 day'
    )
  $$
) AS (
    entity TEXT
    , "day 1" TEXT             -- add as many as you need
    , "day 2" TEXT
    , "day 3" TEXT
);

 entity |  day 1   |  day 2   |  day 3   
--------+----------+----------+----------
 a      |  100.00% |  100.00% | 
 b      |    0.00% |    0.00% | 
 c      |  100.00% |          | 
 d      |  100.00% |          | 
 e      |          |          |   75.00%

As a final remark, the second argument of CROSSTAB can be something like

$$ 
WITH n(ow) AS (VALUES(DATE_TRUNC('day', NOW()))) 
SELECT GENERATE_SERIES(n.ow + '-2 days', n.ow, '1 day') FROM n
$$

and the query would always return a dynamic report for the last three days (today included).

Answer 2

A slight variation of your request, with equivalent response (I think).

First, let's assume this is our starting data:

CREATE TABLE t
(
    date_time timestamp NOT NULL,
    entity text NOT NULL,
    result boolean NOT NULL,  /* true means 'success', false 'fail' */
    PRIMARY KEY(date_time, entity, result)
) ;

INSERT INTO
    t
    (date_time, entity, result)
VALUES
    ('2016-01-01 11:00', 'a', true),
    ('2016-01-01 17:00', 'a', true),    -- two events for a on same day
    ('2016-01-01 11:01', 'b', false),
    ('2016-01-01 11:03', 'c', true),
    ('2016-01-01 13:00', 'd', true),    -- only one event for d
    ('2016-01-02 11:00', 'a', true),
    ('2016-01-02 11:01', 'b', false),
    ('2016-01-03 11:03', 'e', true),     -- 75% success 'e' on day 3 
    ('2016-01-03 11:04', 'e', true),
    ('2016-01-03 11:05', 'e', true),
    ('2016-01-03 11:06', 'e', false) ;

Intermediate "success_summary" table

We create a (temporary intermediate) table wich I'll call "success_summary" which has all the different success rates for all the different entities and days (where they actually happen):

CREATE TEMPORARY TABLE success_summary
AS
SELECT
    date_period, 
    entity, 
    /* If needed, following line gives approx. your aggs. */
    /* array[count_successes + count_failures, count_successes, count_failures] AS summary, */
    /* Next computation renders the percentage of success as etxt */
    to_char(
         count_successes::double precision*100.0 / (count_successes + count_failures), 
         '990.00%') AS pct_text
FROM
(
    SELECT
        /* date_trunc('day', date_time) AS date_period */
        date_time::date AS date_period, 
        entity, 
        /* We count successes and failures.  We profit from the facxt that CASE always has an implicit ELSE NULL */
        count(CASE WHEN     result THEN 1 END) AS count_successes, 
        count(CASE WHEN not result THEN 1 END) AS count_failures
    FROM
        t
    GROUP BY
        date_period, entity 
) AS q1 ;

The table contains at this point:

SELECT to_char(date_period, 'yyyy-mm-dd') AS date_period, entity, pct_text 
FROM success_summary 
ORDER BY entity, date_period;

| date_period | entity | pct_text |
|-------------|--------|----------|
|  2016-01-01 |      a |  100.00% |
|  2016-01-02 |      a |  100.00% |
|  2016-01-01 |      b |    0.00% |
|  2016-01-02 |      b |    0.00% |
|  2016-01-01 |      c |  100.00% |
|  2016-01-01 |      d |  100.00% |
|  2016-01-03 |      e |   75.00% |

Intermediate "all_success_summary" table

Now, in order to be able to (easily) crosstab we need to "fill in" all the missing values, so that everything is filled in our rectangular matrix. This means, for instance, that there is a row with (2016-01-01, 'a', *something*) values, which is actually not present in the previous table. (NOTE: I've chosen NULL to be the something, but you could use a text such as 'N/A' by using a coalesce(pct_text, 'N/A') instead of pct_text).

We do so by using yet another intermediate table, making a cartesian product of (date_periods) x (entities):

CREATE TEMPORARY TABLE all_success_summary AS
SELECT 
    date_period, entity, pct_text
FROM
(
  -- Cross join to have all (date_period, entity) possible pairs
    (SELECT DISTINCT date_period FROM success_summary) AS q00
    CROSS JOIN
    (SELECT DISTINCT      entity FROM success_summary) AS q01
) AS q0
-- Left join with original data to retrieve actual pct_text
-- where it exists (it will be NULL, otherwise)
LEFT JOIN success_summary USING(date_period, entity) ;

The content of this intermediate table is:

| date_period | entity | pct_text |
|-------------|--------|----------|
|  2016-01-01 |      a |  100.00% |
|  2016-01-02 |      a |  100.00% |
|  2016-01-03 |      a |   (null) |
|  2016-01-01 |      b |    0.00% |
|  2016-01-02 |      b |    0.00% |
|  2016-01-03 |      b |   (null) |
|  2016-01-01 |      c |  100.00% |
|  2016-01-02 |      c |   (null) |
|  2016-01-03 |      c |   (null) |
|  2016-01-01 |      d |  100.00% |
|  2016-01-02 |      d |   (null) |
|  2016-01-03 |      d |   (null) |
|  2016-01-01 |      e |   (null) |
|  2016-01-02 |      e |   (null) |
|  2016-01-03 |      e |   75.00% |

Final PIVOT table

At this point, we can use the first version of crosstab to get all the data PIVOTed:

SELECT
    *
FROM
    crosstab(
        'SELECT entity, date_period, pct_text 
           FROM all_success_summary 
           ORDER BY entity, date_period')
    AS ct (entity text, "2016-01-01" text, "2016-01-02" text, "2016-01-03" text) ;

the resulting table is:

| entity | 2016-01-01 | 2016-01-02 | 2016-01-03 |
|--------|------------|------------|------------|
|     a  |   100.00%  |   100.00%  |            | 
|     b  |     0.00%  |     0.00%  |            |  
|     c  |   100.00%  |            |            |  
|     d  |   100.00%  |            |            |  
|     e  |            |            |     75.00% |

It doesn't contain the aggregate vectors or any other representation, but the formatted result you could directly import to a SpreadSheet.

NOTES

1: In order to know which is the appropriate column definition, required when using the CrossTab function, you can use the following query:

SELECT
    '(entity text, ' || string_agg(c, ', ') || ')' AS column_definition
FROM
(
    SELECT DISTINCT
        '"' || date_period || '" text' AS c
    FROM
        all_success_summary 
    ORDER BY
        c 
) AS q1 ;

2: I've chosen "date_period" to be just one day (and, in some places, formatted the result for ease of display). All the same can be achieved by using something such as date_trunc('week', date_time) AS date_period, to summarize by weeks instead of days, instead of the definition I used. This can be generalized to any type of grouping.

3: If you still want to have your arrays, there's a hint on the definition of success_summary on where you'd start getting them.

4: The intermediate tables can be skipped alltogether (by redundantly putting their definition where there name appears). They can also be hidden within a user defined function, that would drop them after use. You cannot avoid them by means of a CTE, because crosstab won't "understand" the virtual tables created by the WITH statement. Anyhow, as you normally need also the column_definition... the temporary tables come in handy.

5: There are other variations, instead of using crosstab you could also get all the rows from all_success_summary in JSON format, and have this information post-processed. It all depends on your specific use-case (but I've seen a screenshot of an Excel... and I moved in the closest direction ;-). I must say that Excel can PIVOT itself all the data from just the "success_summary" data (and probably also from the original one).

---

You can check most of this (except the crosstab itself) at SQLFiddle.

Answer 3

-- Test data
drop table if exists t cascade;
create table t(datetime timestamptz, entity char(1), result bool);
insert into t values
  ('2016-01-01 11:00:01', 'a', true),
  ('2016-01-01 17:00:01', 'a', true),  -- two events for :a on same day
  ('2016-01-01 11:01:01', 'b', false),
  ('2016-01-01 11:03:01', 'c', true),
  ('2016-01-01 13:00:01', 'd', true),  -- only one event for :d
  ('2016-01-02 11:00:01', 'a', true),
  ('2016-01-02 11:01:01', 'b', false),
  ('2016-01-02 11:03:01', 'c', true);

do $$ -- Here we will create the view to select desired data
declare
  select_clause text := 'entity';
  dates date[];
  d date;
  date_filter text;
begin
  -- Generate array with dates for our columns
  select array_agg(dt) into dates
  from generate_series((select min(datetime) from t)::date, (select max(datetime) from t)::date, '1 day') as dt;
  raise info '%', dates;

  -- Generate "select part"
  foreach d in array dates loop
    date_filter := format('datetime::date = %L', d);
    raise notice '%', date_filter;
    select_clause :=
      select_clause ||
      ', array[count(*) filter(where ' || date_filter ||
      '), count(*) filter(where ' || date_filter ||
      ' and result), count(*) filter(where ' || date_filter ||
      ' and not result)] as ' ||
      quote_ident(d::text);
  end loop;
  raise info '%', select_clause;

  -- Create temporary view using previously generated "select part"
  -- "temp view" is session-wide
  execute 'create or replace temp view v as select ' || select_clause || ' from t group by entity';
end $$;

select * from v order by entity;

http://rextester.com/OLISJ76343