甲骨文:枢轴(凝结)一些计数到一行吗?

问题描述

更新:我所说的 coalesce 我应该一直称​​ pivot .

update: what I was calling coalesce I should have been calling pivot.

我正在从日志表中提取一些日常使用计数.我可以很容易地将每个日期/项目的数据获取到一行，但是我想将列透视成一行.

I'm extracting some daily usage counts from a log table. I can easily get this data one row per date/item, but I would like to pivot the columns into a single row.

例如，我有

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查询.

Here's my current 10g query.

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具有 pivot 操作.接受的答案显示了如何在9i和10g中做到这一点.

update: 11g has a pivot operation. The accepted answer shows how to do this in 9i and 10g.

推荐答案

您要查找的内容是透视-将行数据转换为列式.

What you're looking for is pivoting - transposing the row data into columnar.

使用:

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等效

---

使用:

Non-WITH/CTE Equivalent

---

Use:

  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语句更改为Oracle特定的IF/ELSE逻辑DECODE.

Pre Oracle9i would need the CASE statements changed to DECODE, Oracle specific IF/ELSE logic.

未经测试:

  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

这篇关于甲骨文:枢轴(凝结)一些计数到一行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！