本文介绍了每小时数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想显示以下给定记录的数据透视表:
I want to display a pivot table for the given below records:
我有一个名为 fooo
的表包含两列:
I have a table called fooo
with the two columns:
create table fooo (
cola varchar(5),
colb time
);
插入一些记录:
insert into fooo values
('a','10:00:00'), ('a','10:30:00'), ('b','20:00:00'), ('c','13:00:00')
, ('d','01:00:00'), ('e','10:10:00'), ('f','02:01:00'), ('g','02:20:00')
, ('h','04:00:00'), ('i','05:00:00'), ('j','06:00:00'), ('k','07:00:00')
, ('l','08:00:00'), ('m','09:00:00'), ('n','22:00:00'), ('o','23:00:00');
预期的输出应类似于以下格式:
And the expected output should be looks like the following format:
预期输出:
cola 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
-------------------------------------------------------------------------------------------------
a 2
b 1
c 1
d 1
e 1
f 1
g 1
h 1
i 1
j 1
k 1
l 1
m 1
n 1
o 1
推荐答案
SELECT * FROM crosstab(
'SELECT cola, EXTRACT(HOUR from colb) AS h, count(*) AS ct
FROM fooo
GROUP BY 1, 2
ORDER BY 1, 2'
,'SELECT g::float8 FROM generate_series(0,23) g'
) AS ct (cola text
, h00 int, h01 int, h02 int, h03 int, h04 int, h05 int
, h06 int, h07 int, h08 int, h09 int, h10 int, h11 int
, h12 int, h13 int, h14 int, h15 int, h16 int, h17 int
, h18 int, h19 int, h20 int, h21 int, h22 int, h23 int);
另外:
您忘记了0/24小时。
使用带引号的列名可以避免使用双引号。
Asides:
You forgot the hour 0 / 24.
Using column names with leading character to obviate the need for double-quotes.
详细信息:
- PostgreSQL Crosstab Query
这篇关于每小时数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!