SQL問題求助(修正)

ms sql sql

CooperWu 2022-06-22 10:25:39 ‧ 1071 瀏覽

分享至

SQL範例
各位不好意思，昨天我有發問而且已經有解答了，但我漏掉了一個地方，所以修正後重新再發問一次

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

跟昨天的差別在於多了一欄狀態需要判斷
再次麻煩各位抽空解答，感謝

登入發表討論

直播研討會

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

直播中

1 個回答

rogeryao

iT邦超人 8 級 ‧ 2022-06-22 12:17:49

最佳解答

SELECT A.*,B.Status2
FROM (
-- Status1
SELECT DateTimePerHour,MonitorID,SubTotal / Status1CountSum AS 'AverageValue',Status1
FROM (
SELECT DateTimePerHour,MonitorID,Status1,
SUM(Status1Count) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'Status1CountSum',
SUM(ValuePart) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'SubTotal',
ROW_NUMBER() OVER (PARTITION BY DateTimePerHour,MonitorID ORDER BY Status1Count DESC) AS 'NO'
FROM (
SELECT dateadd(hour, datediff(hour, 0, [Date_Time]), 0) AS 'DateTimePerHour',
MonitorID,Status1,COUNT(*) AS 'Status1Count',SUM(Value) AS 'ValuePart'
FROM tmp
GROUP BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status1
) AS K
) AS Q
WHERE Q.NO = 1 ) AS A
--
INNER JOIN (
-- Status2
SELECT DateTimePerHour,MonitorID,
-- SubTotal / Status2CountSum AS 'AverageValue',
Status2
FROM (
SELECT DateTimePerHour,MonitorID,Status2,
-- SUM(Status2Count) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'Status2CountSum',
-- SUM(ValuePart) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'SubTotal',
ROW_NUMBER() OVER (PARTITION BY DateTimePerHour,MonitorID ORDER BY Status2Count DESC) AS 'NO'
FROM (
SELECT dateadd(hour, datediff(hour, 0, [Date_Time]), 0) AS 'DateTimePerHour',
MonitorID,Status2,COUNT(*) AS 'Status2Count'
-- ,SUM(Value) AS 'ValuePart'
FROM tmp
GROUP BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status2
) AS K
) AS Q
WHERE Q.NO = 1 
) AS B ON B.DateTimePerHour = A.DateTimePerHour AND B.MonitorID = A.MonitorID
-- AND B.AverageValue = A.AverageValue

Demo