使用字符串值变量的双向频率表的 SQL

Question

考虑 PostgreSQL 9.4 中的以下事件数据:

      eventTime     | eventName
2015-09-25 18:00:00 | 'AAA'
2015-09-25 17:00:00 | 'BBB'
2015-09-25 16:00:00 | 'BBB'
2015-09-25 15:00:00 | 'BBB'
2015-09-25 14:00:00 | 'AAA'
2015-09-26 13:00:00 | 'CCC'
2015-09-26 12:00:00 | 'AAA'
2015-09-26 11:00:00 | 'BBB'
2015-09-26 10:00:00 | 'CCC'
2015-09-26 09:00:00 | 'BBB'
2015-09-27 08:00:00 | 'AAA'
2015-09-27 07:00:00 | 'CCC'
2015-09-27 05:00:00 | 'CCC'
2015-09-27 04:00:00 | 'CCC'
2015-09-27 03:00:00 | 'CCC'
2015-09-27 02:00:00 | 'AAA'

虽然单个基于 count() 的表很简单，例如:

SELECT eventTime, count(1)
  from (SELECT data->>'eventName' as eventName,
        date_trunc('day', to_timestamp(data->>'timestamp','YYYY-MM-DDZHH24:MI:SS.MS')::timestamp without time zone) AS eventTime
        FROM sidetrack where (data->>'eventName' = 'AAA') IS TRUE) AS tmptab
GROUP BY eventTime
ORDER BY eventTime ASC

只能统计 eventName 的单个值的出现次数。我对 SQL 不是很有经验，并且正在努力寻找一种方法来创建双向频率表。在此示例中，结果将是:

     day    | 'AAA' | 'BBB' | 'CCC'
------------+-------+-------+-------
 2015-09-25 |    2  |    3  |    0
 2015-09-26 |    1  |    2  |    2
 2015-09-27 |    2  |    0  |    4

有计算数值变量的例子使用 with_bucket()，但这并不能推广到字符串值字段。

我试过在 WITH 下进行嵌套选择，例如:

WITH
    foo AS (
        SELECT ...
    ),
    bar AS (
        SELECT ...
    FROM foo
    ),
SELECT *
FROM bar;

还有外部 JOINS，但我无法破解。

Answer 1

您可以只对每一列使用 CASE 语句来为匹配项生成 1，然后对所有行求和，例如；

SELECT date_trunc('day', timestamp) AS time,
       SUM(CASE WHEN "eventName" = 'AAA' THEN 1 ELSE 0 END) AAA,
       SUM(CASE WHEN "eventName" = 'BBB' THEN 1 ELSE 0 END) BBB,
       SUM(CASE WHEN "eventName" = 'CCC' THEN 1 ELSE 0 END) CCC
FROM sidetrack
GROUP BY date_trunc('day', timestamp)
ORDER BY date_trunc('day', timestamp) ASC

An SQLfiddle to test with.