iT邦幫忙

0

SQL問題求助

  • 分享至 

  • xImage

SQL範例
大家好,有個SQL問題想要請教

表A是原始資料,每個測項固定六分鐘會有一筆數值及狀態
表B是這次想求的結果。以小時GroupBy去計算出平均值,每六分鐘一筆資料,所以一小時會是十筆資料,這邊需要去計算這十筆資料哪個狀態是出現最多次的
表C是計算各個狀態的數量,由前三行可以知道狀態1的出現3次,2的4次,3的3次,所以表B的第一行狀態就是出現最多次的狀態2

再麻煩各位抽空解答,感恩

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

2
rogeryao
iT邦超人 7 級 ‧ 2022-06-22 08:20:04
最佳解答
SELECT DateTimePerHour,MonitorID,SubTotal / StatusCountSum AS 'AverageValue',Status
FROM (
SELECT DateTimePerHour,MonitorID,Status,
SUM(StatusCount) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'StatusCountSum',
SUM(ValuePart) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'SubTotal',
ROW_NUMBER() OVER (PARTITION BY DateTimePerHour,MonitorID ORDER BY StatusCount DESC) AS 'NO'
FROM (
SELECT dateadd(hour, datediff(hour, 0, [Date_Time]), 0) AS 'DateTimePerHour',
MonitorID,Status,COUNT(*) AS 'StatusCount',SUM(Value) AS 'ValuePart'
FROM tmp
GROUP BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status
) AS K
) AS Q
WHERE Q.NO = 1

Demo

參考解法 : 趣味SQL,求出前十大客戶各自的前十大商品合計銷售額

1
純真的人
iT邦大師 1 級 ‧ 2022-06-21 15:42:37

恩?你是要撈最大的筆數資料吧?

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c9cbc8f5eacda8e924b1999276a3eebd

select DateTimePerHour
,MonitorID
,Status
,StatusCount
from (
Select dateadd(hour, datediff(hour, 0, [Date_Time]), 0) as 'DateTimePerHour'
  ,MonitorID
  ,Status
  ,count(*) StatusCount
  ,Row_Number()over(
    PARTITION BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0) 
    ,MonitorID
    order by count(*) desc
  ) Sort
  From tmp
  group by dateadd(hour, datediff(hour, 0, [Date_Time]), 0)
  ,Status
  ,MonitorID
) k
where Sort = 1

https://ithelp.ithome.com.tw/upload/images/20220621/20061369kyyRsBRfoj.png

CooperWu iT邦新手 3 級 ‧ 2022-06-21 16:30:18 檢舉

是這樣沒錯,但是最後的結果要放到表B

照你推估應該會這樣~不過秒數有篩掉一些
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9c9ca3d5dc9888459cbd9e29c0b27981

select DateTimePerHour
,MonitorID
,AverageValue
,Status
from (
Select dateadd(hour, datediff(hour, 0, [Date_Time]), 0) as 'DateTimePerHour'
  ,MonitorID
  ,Status
  ,AVG(Value) AverageValue
  ,count(*) StatusCount
  ,Row_Number()over(
    PARTITION BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0) 
    ,MonitorID
    order by count(*) desc
  ) Sort
  From tmp
  group by dateadd(hour, datediff(hour, 0, [Date_Time]), 0)
  ,Status
  ,MonitorID
) k
where Sort = 1

https://ithelp.ithome.com.tw/upload/images/20220621/20061369v7ohzr0SXR.png

CooperWu iT邦新手 3 級 ‧ 2022-06-22 08:47:46 檢舉

感謝您的回答,不過rogeryao的解答是我需要的結果,最佳解就給他了

我要發表回答

立即登入回答