[MSSQL] 使用GROUP BY...建立新表後，希望再新增欄位是透過新表運算的

mssql sql

HoyaT 2021-05-20 12:53:22 ‧ 782 瀏覽

各位先進好
想請問該如何把運算欄位實際的新增到原本的列表中
以下舉例

上圖為RAW DATA, 取樣間隔約每日數十筆資料

USE HMI_DB
SELECT MAX([TIME]) AS [Log]
	  ,MAX(convert(date, [Time])) as [Date]
	  ,MAX(CAST(tb_OfficePowerMeter.Total_Act_Energy  AS decimal(18,2))) AS Total_Act_Power
  INTO HMI_DB.dbo.tb_OfficePowerMeter_Daily
  FROM [HMI_DB].[dbo].[tb_OfficePowerMeter]
  GROUP BY DATEPART(MONTH, [Time]),DATEPART(day, [Time]);

上圖是日報表，將每日資料的最後一筆取出來建立一組新表，會得到如下圖

此時我需要記算每日用電量，因此又在搜尋時多了一組欄位如下

  select [Log],[Date],Total_Act_Power
	 **,Total_Act_Power-LAG(Total_Act_Power,1,Total_Act_Power) OVER (ORDER BY [Log]) AS 本日用電量 **
	from HMI_DB.dbo.tb_OfficePowerMeter_Daily

現在問題來了
1.有辦法將　本日用電量計算的欄位，在建立新表的時候就一併建立進去嗎？實際使用會與GROUP BY指令干涉

2.如果沒辦法的話，我如何將　＂本日用電量＂這個運算欄位寫入至這張新表中？

登入發表討論

直播研討會

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

直播中

2 個回答

rogeryao

iT邦超人 8 級 ‧ 2021-05-20 13:47:14

最佳解答

CREATE TABLE aa (
  a1 varchar(20), -- Time
  a2 float        -- Total_Act_Energy
);

INSERT INTO aa
VALUES ('2021-05-10 18:15', 425.8),
  ('2021-05-10 19:15', 525.8),
  ('2021-05-10 20:15', 625.8),
  ('2021-05-11 18:15', 427.1),
  ('2021-05-11 19:15', 527.1),
  ('2021-05-11 20:15', 627.1),
  ('2021-05-11 21:15', 727.1),
  ('2021-05-12 18:16', 428.3),
  ('2021-05-12 19:16', 528.3),
  ('2021-05-13 09:47', 428.7);

CREATE TABLE bb (
  b1 varchar(20),
  b2 varchar(20),
  b3 float,
  b4 float
);

INSERT INTO bb (b1, b2, b3, b4)
SELECT a1,a3,a2,ROUND(a2 - LAG(a2, 1, a2) OVER (ORDER BY a1), 2) AS a4
FROM (
  SELECT a1,CONVERT(varchar(10), a1, 120) AS a3,a2
  FROM (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY CONVERT(varchar(10), a1, 120) ORDER BY a1 DESC) AS roworder
    FROM aa) AS m
  WHERE roworder = 1) AS k
ORDER BY a1

Demo
aa : [HMI_DB].[dbo].[tb_OfficePowerMeter]
bb : HMI_DB.dbo.tb_OfficePowerMeter_Daily

回應 1
分享
檢舉

HoyaT iT邦新手 5 級 ‧ 2021-05-21 17:15:42 檢舉

SELECT [Time],[Date],[Total_Act_Energy]
		,ROUND([Total_Act_Energy] - LAG([Total_Act_Energy], 1, [Total_Act_Energy]) OVER (ORDER BY [Time]), 2) AS 本日用電量
	   ,ROUND([Total_Act_Energy] - LAG([Total_Act_Energy], 1, [Total_Act_Energy]) OVER (ORDER BY [Time]), 2) * 3.2 AS 本日估算電費
FROM (
  SELECT [Time],CONVERT(varchar(10), [Time], 120) AS [Date],[Total_Act_Energy]
  FROM (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY CONVERT(varchar(10), [Time], 120) ORDER BY [Time] DESC) AS roworder
    FROM [dbo].[tb_OfficePowerMeter]) AS m
  WHERE roworder = 1) AS k
ORDER BY [Time]

成功了! <3

登入發表回應

石頭

iT邦高手 1 級 ‧ 2021-05-20 13:05:16

你可以嘗試使用Subquery或CTE去接你的現在結果集在使用LAG window function.

;WITH CTE AS (
	SELECT MAX([TIME]) AS [Log]
	  ,MAX(convert(date, [Time])) as [Date]
	  ,MAX(CAST(tb_OfficePowerMeter.Total_Act_Energy  AS decimal(18,2))) AS Total_Act_Power
	INTO HMI_DB.dbo.tb_OfficePowerMeter_Daily
	FROM [HMI_DB].[dbo].[tb_OfficePowerMeter]
	GROUP BY DATEPART(MONTH, [Time]),DATEPART(day, [Time])
)
SELECT [Log],
       [Date],
	   Total_Act_Power,
	   Total_Act_Power-LAG(Total_Act_Power,1,Total_Act_Power) OVER (ORDER BY [Log]) AS '本日用電量'
FROM CTE