1. 지역 코드 저장 테이블 

-- region_code definition

CREATE TABLE `region_code` (

  `city_code` int NOT NULL,

  `city` varchar(10) NOT NULL,

  `gu_code` int NOT NULL,

  `gu` varchar(20) NOT NULL,

  `area` decimal(10,2) DEFAULT NULL,

  PRIMARY KEY (`city_code`,`gu_code`),

  UNIQUE KEY `region_code_UN` (`gu_code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

 

2. 법정동 코드 저장 테이블

 -- region_dong definition

CREATE TABLE `region_dong` (

  `city` varchar(10) NOT NULL,

  `gu` varchar(20) NOT NULL,

  `dong` varchar(30) NOT NULL,

  `city_code` int NOT NULL,

  `gu_code` int NOT NULL,

  PRIMARY KEY (`gu_code`,`dong`),

  KEY `idx_region_dong` (`gu_code`),

  KEY `idx_city_gu_dong` (`city`,`gu`,`dong`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

3. apt view 테이블

-- apt_deal_sales definition

CREATE TABLE `apt_deal_sales` (

  `id` int unsigned NOT NULL AUTO_INCREMENT,

  `regional_code` int NOT NULL,

  `dong` varchar(30) NOT NULL,

  `jibun` varchar(20) DEFAULT '',

  `apartment_name` varchar(80) NOT NULL,

  `area` float NOT NULL DEFAULT '0',

  `area_str` varchar(8) DEFAULT NULL,

  `max_deal_amount` int DEFAULT '0',

  `curr_deal_amount` int DEFAULT '0',

  `percent` float DEFAULT '0',

  `deal_date` date DEFAULT NULL,

  `build_year` int unsigned DEFAULT NULL,

  `trade_type` int DEFAULT NULL,

  `road_name` varchar(150) DEFAULT NULL,

  `road_name_bonbun_bubun` varchar(30) DEFAULT NULL,

  `apt_seq` varchar(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_apt_deal_sales2` (`regional_code`,`dong`,`jibun`,`apartment_name`,`area_str`),

  KEY `idx_deal_date` (`deal_date`),

  KEY `idx_curr_deal_amount` (`curr_deal_amount`),

  KEY `idx_apt_deal_sales_regional_code` (`regional_code`),

  KEY `idx_apt_deal_sales_seq_area_date` (`apt_seq`,`area_str`,`deal_date`)

) ENGINE=InnoDB AUTO_INCREMENT=1294852 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

 

 

4. apt_deal_sales에 저장할 데이터 조회하는 쿼리

SELECT

  a.apt_seq AS aptSeq,

  a.area_str AS areaStr,

  a.regional_code AS regionalCode,

  a.dong AS dong,

  a.jibun AS jibun,

  a.apartment_name AS apartmentName,

  a.deal_date AS dealDate,

  a.deal_amount AS currDealAmount,

  a.build_year AS buildYear,

  a.trade_type AS tradeType,

  a.road_name AS roadName,

  a.road_name_bonbun_bubun AS roadNameBonbunBubun,

  -- 해당 그룹의 최대 거래금액

  (SELECT MAX(x.deal_amount)

   FROM apt_sales x

   WHERE x.apt_seq = a.apt_seq AND x.area_str = a.area_str

  ) AS maxDealAmount

FROM apt_sales a

JOIN (

  -- 최신 거래일(max_deal_date) 가져오기

  SELECT apt_seq, area_str, MAX(deal_date) AS max_deal_date

  FROM apt_sales

  GROUP BY apt_seq, area_str

) t

  ON a.apt_seq = t.apt_seq

  AND a.area_str = t.area_str

  AND a.deal_date = t.max_deal_date

추천0 비추천 0