SQL問題求助

ms sql sql

CooperWu 2022-06-21 15:01:04 ‧ 1360 瀏覽

分享至

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

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

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

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

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

回應
分享
檢舉

登入發表回應

純真的人

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

回應 3
分享
檢舉

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

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

純真的人 iT邦大師 1 級 ‧ 2022-06-21 17:02:25 檢舉

照你推估應該會這樣~不過秒數有篩掉一些
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