多个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 | 
 ... 
 

这是 CASE ）

这里是 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 HOURI 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？需要按每月小时计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！