SQL Server 整理實價登錄資料：清洗、彙總、預存程式與 Python 自動化

-- 暫存表 #實價明細1
DROP TABLE IF EXISTS #實價明細1
SELECT *,
    SUBSTRING(編號, 15, 1) AS 縣市代碼,
    建物移轉總面積平方公尺 * 0.3025 AS 建物移轉總面積坪
INTO #實價明細1
FROM [tasker].[dbo].[house_price]
WHERE 單價元平方公尺 IS NOT NULL
  AND TRY_CAST(單價元平方公尺 AS FLOAT) <> 0
  AND 交易年月日 >= '1110101'
  AND LEN(交易年月日) = 7
  AND (LEN(建築完成年月) = 7 OR 建築完成年月 IS NULL)
  AND 主要用途 = '住家用'
  AND 交易標的 IN ('房地(土地+建物)', '房地(土地+建物)+車位')
  AND (建物型態 LIKE '%住宅%' OR 建物型態 LIKE '%公寓%'
       OR 建物型態 LIKE '%套房%' OR 建物型態 LIKE '%華廈%'
       OR 建物型態 = '透天厝');

-- 暫存表 #實價明細2
DROP TABLE IF EXISTS #實價明細2
SELECT 
  CASE 縣市代碼 WHEN 'A' THEN '台北市' WHEN 'B' THEN '台中市' WHEN 'C' THEN '基隆市' WHEN 'D' THEN '台南市'
               WHEN 'E' THEN '高雄市' WHEN 'F' THEN '新北市' WHEN 'G' THEN '宜蘭縣' WHEN 'H' THEN '桃園縣'
               WHEN 'I' THEN '嘉義市' WHEN 'J' THEN '新竹縣' WHEN 'K' THEN '苗栗縣' WHEN 'L' THEN '台中縣'
               WHEN 'M' THEN '南投縣' WHEN 'N' THEN '彰化縣' WHEN 'O' THEN '新竹市' WHEN 'P' THEN '雲林縣'
               WHEN 'Q' THEN '嘉義縣' WHEN 'R' THEN '台南縣' WHEN 'S' THEN '高雄縣' WHEN 'T' THEN '屏東縣'
               WHEN 'U' THEN '花蓮縣' WHEN 'V' THEN '台東縣' WHEN 'W' THEN '金門縣' WHEN 'X' THEN '澎湖縣'
               WHEN 'Y' THEN '陽明山' WHEN 'Z' THEN '連江縣' END AS 縣市,
  *,
  ROUND(建物移轉總面積坪, 1) AS 建物移轉總面積坪,
  CASE 
    WHEN 建物移轉總面積坪 <= 20 THEN '小坪數'
    WHEN 建物移轉總面積坪 <= 40 THEN '中坪數'
    WHEN 建物移轉總面積坪 <= 80 THEN '大坪數'
    ELSE '豪宅' 
  END AS 建物大小分類,
  ROUND(CAST(單價元平方公尺 AS FLOAT) / 0.3025, 0) AS 單價元坪,
  CAST(總價元 AS INT) / 10000 AS 總價萬,
  CAST(LEFT(交易年月日, 3) AS FLOAT) - CAST(LEFT(建築完成年月, 3) AS FLOAT) AS 屋齡,
  LEFT(交易年月日, 5) AS 交易年月,
  LEFT(交易年月日, 3) AS 交易年份
INTO #實價明細2
FROM #實價明細1;

-- 建立去年同期比對表
DROP TABLE IF EXISTS #縣市比對表
SELECT 縣市, 不動產類別, 交易年月,
       交易年月 + 100 AS 比對交易年月,
       ROUND(AVG(單價元坪) / 10000, 2) AS 單價萬坪,
       COUNT(DISTINCT 編號) AS 案件數,
       GETDATE() AS 更新時間
INTO #縣市比對表
FROM #實價明細2
WHERE 縣市 IS NOT NULL
GROUP BY 縣市, 不動產類別, 交易年月;

-- 合併去年同期房價
SELECT a.*, b.單價萬坪 AS 去年同期單價萬坪, b.案件數 AS 去年同期案件數
INTO #縣市彙總表
FROM #縣市比對表 a
LEFT JOIN #縣市比對表 b
  ON a.縣市 = b.縣市 AND a.不動產類別 = b.不動產類別 AND a.交易年月 = b.比對交易年月;

-- 計算漲跌幅
DROP TABLE IF EXISTS #縣市歷史漲幅
SELECT *,
  MAX(漲幅) OVER(PARTITION BY 縣市, 不動產類別 ORDER BY 交易年月) AS 歷史最高漲跌幅,
  MIN(漲幅) OVER(PARTITION BY 縣市, 不動產類別 ORDER BY 交易年月) AS 歷史最低漲跌幅
INTO #縣市歷史漲幅
FROM (
  SELECT *,
    (單價萬坪 - 去年同期單價萬坪) / 去年同期單價萬坪 AS 漲幅
  FROM #縣市彙總表
) a;

-- 寫入正式表
TRUNCATE TABLE dbo.縣市彙總表;
INSERT INTO dbo.縣市彙總表
SELECT a.*, b.交易年月 AS 歷史最高漲跌幅年月, c.交易年月 AS 歷史最低漲跌幅年月
FROM #縣市歷史漲幅 a
LEFT JOIN #縣市歷史漲幅 b
  ON a.縣市 = b.縣市 AND a.不動產類別 = b.不動產類別 AND a.歷史最高漲跌幅 = b.漲幅
LEFT JOIN #縣市歷史漲幅 c
  ON a.縣市 = c.縣市 AND a.不動產類別 = c.不動產類別 AND a.歷史最低漲跌幅 = c.漲幅;

-- 縣市坪數彙總
TRUNCATE TABLE dbo.縣市坪數彙總表;
INSERT INTO dbo.縣市坪數彙總表
SELECT 縣市, 不動產類別, 交易年月, 建物大小分類,
       ROUND(AVG(單價元坪) / 10000, 2) AS 單價萬坪,
       COUNT(DISTINCT 編號) AS 案件數,
       GETDATE() AS 更新時間
FROM #實價明細2
GROUP BY 縣市, 不動產類別, 交易年月, 建物大小分類;

-- 縣市屋齡彙總
TRUNCATE TABLE dbo.縣市屋齡彙總表;
INSERT INTO dbo.縣市屋齡彙總表
SELECT 縣市, 交易年月,
       CASE 
         WHEN 屋齡 <= 10 THEN '1.1~10年'
         WHEN 屋齡 <= 20 THEN '2.10~20年'
         WHEN 屋齡 <= 30 THEN '3.20~30年'
         ELSE '4.30年以上' END AS 屋齡級距,
       ROUND(AVG(單價元坪) / 10000, 2) AS 單價萬坪,
       GETDATE() AS 更新時間
FROM #實價明細2
WHERE 不動產類別 = '中古屋'
GROUP BY 縣市, 交易年月,
         CASE 
           WHEN 屋齡 <= 10 THEN '1.1~10年'
           WHEN 屋齡 <= 20 THEN '2.10~20年'
           WHEN 屋齡 <= 30 THEN '3.20~30年'
           ELSE '4.30年以上' END;

-- 建立比對表
DROP TABLE IF EXISTS #鄉鎮市區比對表;
SELECT 縣市, 鄉鎮市區, 不動產類別, 交易年月, 交易年月+100 AS 比對交易年月,
       ROUND(AVG(單價元坪) / 10000, 2) AS 單價萬坪,
       COUNT(DISTINCT 編號) AS 案件數,
       GETDATE() AS 更新時間
INTO #鄉鎮市區比對表
FROM #實價明細2
WHERE 縣市 IS NOT NULL AND 鄉鎮市區 IS NOT NULL
GROUP BY 縣市, 鄉鎮市區, 不動產類別, 交易年月;

-- 寫入正式表
TRUNCATE TABLE dbo.鄉鎮市區彙總表;
INSERT INTO dbo.鄉鎮市區彙總表
SELECT a.*, b.單價萬坪 AS 去年同期單價萬坪, b.案件數 AS 去年同期案件數
FROM #鄉鎮市區比對表 a
LEFT JOIN #鄉鎮市區比對表 b
  ON a.縣市 = b.縣市 AND a.鄉鎮市區 = b.鄉鎮市區 AND a.不動產類別 = b.不動產類別 AND a.交易年月 = b.比對交易年月;

CREATE PROCEDURE [dbo].[實價登錄分析]
AS
BEGIN
  -- 此處可貼入完整的實價明細清洗、縣市彙總、坪數彙總、屋齡彙總、鄉鎮市區彙總等 SQL 程式碼
  -- 建議每段以 DROP TABLE IF EXISTS 開頭，確保每次重新執行不會衝突
END

CREATE PROCEDURE[dbo].[更新實價登錄]
AS
BEGIN
DROP TABLE IF EXISTS #temp
SELECT
    鄉鎮市區,交易標的,土地位置建物門牌,土地移轉總面積平方公尺,都市土地使用分區,非都市土地使用分區
    ,非都市土地使用編定,交易年月日,交易筆棟數,移轉層次,總樓層數,建物型態,主要用途,主要建材
    ,建築完成年月,建物移轉總面積平方公尺,建物現況格局_房,建物現況格局_廳,建物現況格局_衛,建物現況格局_隔間
    ,有無管理組織,總價元,單價元平方公尺,車位類別,車位移轉總面積平方公尺,車位總價元,編號
    ,不動產類別
INTO #temp
FROM(
SELECT *,ROW_NUMBER() OVER(PARTITION BY 編號 ORDER BY 交易年月日 DESC) AS num
  FROM [tasker].[dbo].[house_price]
) a
where num = 1

TRUNCATE TABLE [tasker].[dbo].[house_price];
INSERT INTO [tasker].[dbo].[house_price]
SELECT * FROM #temp

END

import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};SERVER=你的伺服器;DATABASE=tasker;UID=帳號;PWD=密碼")
cursor = conn.cursor()
cursor.execute("EXEC dbo.更新實價登錄")
conn.commit()
cursor.execute("EXEC dbo.實價登錄分析")
conn.commit()