IBM DB2透视充满日期的表

dojqjjoe  于 2022-11-07  发布在  DB2
关注(0)|答案(2)|浏览(153)

我在DB2中有这样的表:

DATE
----------
09/11/2021
06/10/2021
28/11/2021
17/11/2021
11/10/2021
24/11/2021
07/11/2021
30/11/2021

我想计算某个日期在表中出现的次数,并按年和月将其分组,然后按如下方式显示:

| YEAR | OCTOBER | NOVEMBER |
----------------------------
| 2021 |    2    |    6     |
ssm49v7z

ssm49v7z1#

由于月份是一个已知的量,因此可以使用case语句的求和:

select year(datecol) as year
  ,sum(case when month(datecol) = 1 then 1 else 0 end) as jan
  ,sum(case when month(datecol) = 2 then 1 else 0 end) as feb
  ,sum(case when month(datecol) = 3 then 1 else 0 end) as mar
  ,sum(case when month(datecol) = 4 then 1 else 0 end) as apr
  ,sum(case when month(datecol) = 5 then 1 else 0 end) as may
  ,sum(case when month(datecol) = 6 then 1 else 0 end) as jun
  ,sum(case when month(datecol) = 7 then 1 else 0 end) as jul
  ,sum(case when month(datecol) = 8 then 1 else 0 end) as aug
  ,sum(case when month(datecol) = 9 then 1 else 0 end) as sep
  ,sum(case when month(datecol) = 10 then 1 else 0 end) as oct
  ,sum(case when month(datecol) = 11 then 1 else 0 end) as nov
  ,sum(case when month(datecol) = 12 then 1 else 0 end) as dec
 from datetest 
 group by year(datecol)
 order by 1;

这将给予类似于以下内容的输出:

YEAR        JAN         FEB         MAR         APR         MAY         JUN         JUL         AUG         SEP         OCT         NOV         DEC
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
       2018           0           0           0           0           0           0           0           0           0           0           3           0
       2019           0           0           0           0           0           0           0           0           0           1           2           0
       2020           0           0           0           0           0           0           0           0           0           1           1           0
       2021           0           0           0           0           0           0           0           0           0           2           6           0
sxpgvts3

sxpgvts32#

您可以使用链接dynamic pivot SQL Query in Db2中描述的通用例程。
使用以下调用获取案例所需的结果集:

CALL PIVOT
(
  'SELECT YEAR (DATE) AS YEAR, TO_CHAR (DATE, ''MONTH'') AS MONTH FROM DATES'  
, 'YEAR'
, 'MONTH'
, 'MONTH'
, 'count'
, 'SESSION.PIVOT'
, '-'
, ?, ?, ?
);

其结果是:
| 年份|十一月|十月|
| - -|- -|- -|
| 小行星2021|六个|2个|

相关问题