甲骨文：枢轴（凝结）一些计数到一行吗？

一尘不染

甲骨文：枢轴（凝结）一些计数到一行吗？

sql

更新：我所说的合并我应该一直称透视。

我正在从日志表中提取一些日常使用计数。我可以轻松地获得每日期/项目这一行数据，但我想支点聚结列成一行。

例如，我有：

date    item-to-be-counted count-of-item
10/1    foo                23
10/1    bar                45
10/2    foo                67
10/2    bar                89

我想：

date    count-of-foo     count-of-bar
10/1    23               45
10/2    67               89

这是我当前的10g查询。

select    trunc(started,'HH'),depot,count(*)
  from    logstats
 group by trunc(started,'HH'),depot
 order by trunc(started,'HH'),depot;

TRUNC(STARTED,'HH')       DEPOT      COUNT(*)
------------------------- ---------- --------
10/01/11 01.00.00         foo        28092
10/01/11 01.00.00         bar        2194
10/01/11 02.00.00         foo        3402
10/01/11 02.00.00         bar        1058

更新：11g具有枢轴操作。接受的答案显示了如何在9i和10g中做到这一点。

阅读 122

2021-05-30

共1个答案

一尘不染

您正在寻找的关键-将行数据转换为列式。

Oracle 9i +，使用WITH / CTE：

使用：

WITH summary AS (
    SELECT TRUNC(ls.started,'HH') AS dt,
           ls.depot,
           COUNT(*) AS num_depot
      FROM logstats ls
  GROUP BY TRUNC(ls.started,'HH'), ls.depot)
  SELECT s.dt,
         MAX(CASE WHEN s.depot = 'foo' THEN s.num_depot ELSE 0 END) AS "count_of_foo",
         MAX(CASE WHEN s.depot = 'bar' THEN s.num_depot ELSE 0 END) AS "count_of_bar"
    FROM summary s
GROUP BY s.dt
ORDER BY s.dt

非WITH / CTE等效

使用：

  SELECT s.dt,
         MAX(CASE WHEN s.depot = 'foo' THEN s.num_depot ELSE 0 END) AS "count_of_foo",
         MAX(CASE WHEN s.depot = 'bar' THEN s.num_depot ELSE 0 END) AS "count_of_bar"
    FROM (SELECT TRUNC(ls.started,'HH') AS dt,
                 ls.depot,
                 COUNT(*) AS num_depot
            FROM LOGSTATS ls
        GROUP BY TRUNC(ls.started, 'HH'), ls.depot) s
GROUP BY s.dt
ORDER BY s.dt

在Oracle9i之前的版本中，需要将CASE语句更改为DECODEOracle特定的IF / ELSE逻辑。

Oracle 11g +，使用PIVOT

未经测试：

  SELECT * 
    FROM (SELECT TRUNC(ls.started, 'HH') AS dt,
                 ls.depot
            FROM LOGSTATS ls
        GROUP BY TRUNC(ls.started, 'HH'), ls.depot)
   PIVOT (
     COUNT(*) FOR depot
   )
ORDER BY 1

2021-05-30