多个PIVOTS？需要按每月小时计数 [英] Multiple PIVOTS? Need to count by Hour Per Month
本文介绍了多个PIVOTS？需要按每月小时计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这应该很简单。我有其他的PIVOT SQL查询工作正常。我想统计登录次数：按小时，按月。我正在考虑两个PIVOT或UNPIVOT，然后是PIVOT？是的，我在这里挖掘过，其他网站，谷歌等。我很困扰。
pre $
SELECT
loginid
，DATEPART（MONTH，logtime）月
，DATEPART（HOUR，logtime）小时
FROM somelog（nolock）
）temp
PIVOT（
COUNT （1月，2月，3月，6月，6月，6月，10月，11月，12月）
月份（
b）b
$ b $ / $ c> 我想要的结果是..
HOUR，JAN，FEB ，MAR
00
01
02
..
23
和I不需要8760（365 x 24）他们
我试过GROUP BY HOUR
我试过GROUP BY temp.hour
我也尝试过这种方式。它似乎有效，但我得到了小时00，例如365次..再次，GROUP BY问题..
SELECT
TimeOfDay
，[1] JAN
，[2] FEB
，[3] MAR
，[4] APR
，[5]可能
，[6] JUN
，[7] JUL
，[8] AUG
，[9] SEP
，[10] OCT
，[11] NOV
，[12] DEC
FROM（SELECT logintime
，loginid
，datepart（month，logintime）[month]
，DatePart（hour，logintime）TimeOfDay
sometable（nolock））x $ b $ （[1]，[2]，[3]，[4]，[5]，[6]，[7]，[[]]中的b月份（
COUNT（loginid） 8]，[9]，[10]，[11]，[12]）
）AS pvt
ORDER BY 1,2 b $ b
谢谢，
肯特
解决方案
WITH hour_list AS（
SELECT 0 hour
UNION ALL
SELECT小时+ 1 FROM hour_list WHERE小时< 23
）
SELECT h.hour，
COALESCE（jan，0）jan，
COALESCE（feb，0）feb，
COALESCE（mar，0）mar ，
COALESCE（apr，0）apr，
COALESCE（may，0）may，
COALESCE（jun，0）jun，
COALESCE（jul，0）jul，
COALESCE（aug，0）aug，
COALESCE（sep，0）sep，
COALESCE（oct，0）oct，
COALESCE（nov，0）nov，
COALESCE（dec，0）dec
FROM hour_list h LEFT JOIN
（
SELECT DATEPART（HOUR，logtime）hour，
SUM（CASE WHAT DATEPART（MONTH，logtime）= 1 then 1 end）jan，
SUM（DATEPART（MONTH，logtime）= 2 THEN 1 END时的情况）feb，
SUM（DATEPART（MONTH，logtime）= 3 THEN 1 END时的情况）mar ，
SUM（CASE WHAT DATEPART（MONTH，logtime）= 4 THEN 1 END）apr，
SUM（CASE when DATEPART（MONTH，logtime）= 5 THEN 1 END）may，
SUM （CASE WHAT DATEPART（MONTH，logtime）= 6 THEN 1 END）jun，
SUM（CASE WHEN DATEPART（MONTH，logtime）= 7 THEN 1 END）jul，
SUM（CASE WHAT DATEPART（MONTH，logtime）= 8 THEN 1 END）aug，
SUM（CASE WHAT DATEPART（MONTH，logtime） = 9 THEN 1 END）sep，
SUM（CASE when DATEPART（MONTH，logtime）= 10 THEN 1 END）oct，
SUM（CASE WHAT DATEPART（MONTH，logtime）= 11 THEN 1 END） nov，
SUM（CASE当DATEPART（MONTH，logtime）= 12 THEN 1 END）dec
FROM somelog（NOLOCK）
GROUP BY DATEPART（HOUR，logtime）
）l
ON h.hour = l.hour
或与 PIVOT
WITH hour_list AS（
SELECT 0小时
UNION ALL
SELECT hour + 1 FROM hour_list WHERE hour< 23
）
SELECT h.hour，
COALESCE（[1]，0）jan，
COALESCE（[2]，0）feb，
COALESCE（[ 3]，0）mar，
COALESCE（[4]，0）apr，
COALESCE（[5]，0）可能，
COALESCE（[6]，0） b $ b COALESCE（[7]，0）jul，
COALESCE（[8]，0）aug，
COALESCE（[9]，0）sep，
COALESCE（[10] ，0）oct，
COALESCE（[11]，0）nov，
COALESCE（[12]，0）dec
FROM hour_list h LEFT JOIN
（
SELECT DATEPART（MONTH，logtime）month，
DATEPART（HOUR，logtime）hour，
COUNT（*）log_count $ b $ FROM somelog（NOLOCK）
GROUP BY DATEPART（MONTH，logtime ），DATEPART（HOUR，logtime）
）s
PIVOT
（
SUM（log_count）FOR IN（[1]，[2]，[3]，[4 ]，[5]，[6]，[7]，[8]，[9]，[10]，[11]，[12]）
）p
ON h.hour = p .hour
这两个查询的示例输出：
| HOUR | JAN | FEB | MAR | APR | 5月| JUN | JUL | AUG | SEP | OCT | NOV | DEC |
| ------ | ----- | ----- | ----- | ----- | ----- | ----- | - --- | ----- | ----- | ----- | ----- | ----- |
| 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
...
这是 SQLFiddle <使用 CASE ）
这里是 SQLFiddle demo（使用 PIVOT ）
This should be pretty simple. I have other PIVOT SQL queries working fine. I want to count logins: by hour, by month. I am thinking two PIVOTs or UNPIVOT and then PIVOT? Yes, I have dug around here, other sites, Google, etc. I am pretty stuck.
SELECT
loginid
,DATEPART(MONTH,logtime) Month
, DATEPART(HOUR, logtime) Hour
FROM somelog (nolock)
) temp
PIVOT (
COUNT(loginid)
FOR Month in (JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)
) AS Pvt
What I want the results to be..
HOUR,JAN,FEB,MAR
00
01
02
..
23
and I don't need 8760 (365 x 24) of them
I have tried GROUP BY HOUR
I have tried GROUP BY temp.hour
I have also tried this as well.. It does seem to work, but I get Hour 00 for example 365 times.. Again, the GROUP BY issue..
SELECT
TimeOfDay
, [1] JAN
, [2] FEB
, [3] MAR
, [4] APR
, [5] MAY
, [6] JUN
, [7] JUL
, [8] AUG
, [9] SEP
, [10] OCT
, [11] NOV
, [12] DEC
FROM (SELECT logintime
, loginid
, datepart(month, logintime) [month]
, DatePart(hour, logintime) TimeOfDay
FROM sometable (nolock)) x
PIVOT (
COUNT(loginid)
for [month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pvt
ORDER BY 1, 2
Thanks,
Kent
解决方案 One way to do it
WITH hour_list AS (
SELECT 0 hour
UNION ALL
SELECT hour + 1 FROM hour_list WHERE hour < 23
)
SELECT h.hour,
COALESCE(jan, 0) jan,
COALESCE(feb, 0) feb,
COALESCE(mar, 0) mar,
COALESCE(apr, 0) apr,
COALESCE(may, 0) may,
COALESCE(jun, 0) jun,
COALESCE(jul, 0) jul,
COALESCE(aug, 0) aug,
COALESCE(sep, 0) sep,
COALESCE(oct, 0) oct,
COALESCE(nov, 0) nov,
COALESCE(dec, 0) dec
FROM hour_list h LEFT JOIN
(
SELECT DATEPART(HOUR, logtime) hour,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 1 THEN 1 END) jan,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 2 THEN 1 END) feb,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 3 THEN 1 END) mar,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 4 THEN 1 END) apr,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 5 THEN 1 END) may,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 6 THEN 1 END) jun,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 7 THEN 1 END) jul,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 8 THEN 1 END) aug,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 9 THEN 1 END) sep,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 10 THEN 1 END) oct,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 11 THEN 1 END) nov,
SUM(CASE WHEN DATEPART(MONTH, logtime) = 12 THEN 1 END) dec
FROM somelog (NOLOCK)
GROUP BY DATEPART(HOUR, logtime)
) l
ON h.hour = l.hour
or with PIVOT
WITH hour_list AS (
SELECT 0 hour
UNION ALL
SELECT hour + 1 FROM hour_list WHERE hour < 23
)
SELECT h.hour,
COALESCE([1], 0) jan,
COALESCE([2], 0) feb,
COALESCE([3], 0) mar,
COALESCE([4], 0) apr,
COALESCE([5], 0) may,
COALESCE([6], 0) jun,
COALESCE([7], 0) jul,
COALESCE([8], 0) aug,
COALESCE([9], 0) sep,
COALESCE([10], 0) oct,
COALESCE([11], 0) nov,
COALESCE([12], 0) dec
FROM hour_list h LEFT JOIN
(
SELECT DATEPART(MONTH, logtime) month,
DATEPART(HOUR, logtime) hour,
COUNT(*) log_count
FROM somelog (NOLOCK)
GROUP BY DATEPART(MONTH, logtime), DATEPART(HOUR, logtime)
) s
PIVOT
(
SUM(log_count) FOR month IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) p
ON h.hour = p.hour
Sample output for both queries:
| HOUR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC |
|------|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
| 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
...
Here is SQLFiddle demo (using CASE)
Here is SQLFiddle demo (using PIVOT)
这篇关于多个PIVOTS？需要按每月小时计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
