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

08-18 23:23