多个枢轴?每月需要按小时计算

2022-01-22 00:00:00 pivot group-by sql-server-2008 sql-server

这应该很简单.我有其他 PIVOT SQL 查询工作正常.我想计算登录次数:按小时、按月.我在想两个 PIVOT 还是 UNPIVOT 然后 PIVOT?是的，我已经在这里、其他网站、Google 等进行了挖掘.我被困住了.

选择登录ID,DATEPART(MONTH,logtime) 月, DATEPART(HOUR, logtime) 小时FROM somelog (nolock)) 温度枢轴 (计数(登录名)FOR 月份(1 月、2 月、3 月、4 月、5 月、6 月、7 月、8 月、9 月、10 月、11 月、12 月)) 作为列兵

我想要的结果..

小时，一月，二月，三月

我不需要 8760 (365 x 24) 个

我已尝试按小时分组我试过 GROUP BY temp.hour

我也尝试过这个.它似乎确实有效，但我得到了 00 小时，例如 365 次.再次，GROUP BY 问题..

选择时间, [1] 一月, [2] 二月, [3] 三月, [4] 四月, [5] 五月, [6] 君, [7] 七月, [8] 八月, [9] 九月, [10] 华侨城, [11] 十一月, [12] 十二月FROM(选择登录时间， 登录ID, datepart(month, logintime) [月], DatePart(hour, logintime) TimeOfDayFROM sometable (nolock)) x枢轴 (计数(登录名)对于 [月] 在 ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) 作为列兵按 1、2 订购

谢谢，

肯特

解决方案

一种方法

与 hour_list AS (选择 0 小时联合所有SELECT hour + 1 FROM hour_list WHERE hour <23)选择小时，合并(一月，0)一月，合并(二月，0)二月，合并(三月，0)三月，合并(四月，0)四月，COALESCE(may, 0) 可能，COALESCE(君, 0) 君,合并(七月，0)七月，合并(八月，0)八月，COALESCE(sep, 0) sep,COALESCE(oct, 0) oct,合并(十一月，0)十一月，COALESCE(dec, 0) decFROM hour_list h 左连接(SELECT DATEPART(HOUR, logtime) 小时，SUM(DATEPART(MONTH，logtime)= 1 THEN 1 END)一月的情况，SUM(DATEPART(MONTH，logtime)= 2 THEN 1 END)2 月的情况，SUM(DATEPART(MONTH，logtime)= 3 THEN 1 END)三月的情况，SUM(DATEPART(MONTH，logtime)= 4 THEN 1 END)四月的情况，SUM(CASE WHEN DATEPART(MONTH, logtime) = 5 THEN 1 END) 可能，SUM(DATEPART(MONTH，logtime)= 6 THEN 1 END)的情况SUM(DATEPART(MONTH，logtime)= 7 THEN 1 END)七月的情况，SUM(DATEPART(MONTH，logtime)= 8 THEN 1 END)八月的情况，SUM(DATEPART(MONTH，logtime)= 9 THEN 1 END)9 月的情况，总和(DATEPART(月，logtime)= 10 THEN 1 END 时的情况)oct，SUM(DATEPART(MONTH，logtime)= 11 THEN 1 END)十一月的情况，SUM(DATEPART(月，logtime)= 12 THEN 1 END 时的情况)dec来自 somelog (NOLOCK)按日期分组(小时，日志时间)) lON h.hour = l.hour

或使用 PIVOT

与 hour_list AS (选择 0 小时联合所有SELECT hour + 1 FROM hour_list WHERE hour <23)选择小时，合并([1]，0)一月，合并([2]，0)二月，合并([3]，0)三月，合并([4]，0)四月，COALESCE([5], 0) 可能，COALESCE([6], 0) 君，COALESCE([7], 0) 七月，合并([8]，0)八月，COALESCE([9], 0) 九月，COALESCE([10], 0) 八进制，COALESCE([11], 0) 十一月，COALESCE([12], 0) decFROM hour_list h 左连接(SELECT DATEPART(MONTH, logtime) 月，DATEPART(HOUR, logtime) 小时，COUNT(*) log_count来自 somelog (NOLOCK)GROUP BY DATEPART(MONTH, logtime), DATEPART(HOUR, logtime)) 年代枢(SUM(log_count) FOR 月份 IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pON h.hour = p.hour

两个查询的示例输出:

<上一页>|小时 |一月 |二月 |三月 |四月 |五月 |六月 |七月 |八月 |九月 |华侨城 |十一月 |十二月 ||-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----||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 演示(使用 案例)
这是 SQLFiddle 演示(使用 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)


	
		相关文章