가격 변동 및 트렌드 분석(SQLite.SQL)

1. 가격 변동 및 트렌드 분석

1.1. 매매가 변동률 (연도별 평균 매매가 변화율)

 

SELECT strftime('%Y', ContractYearMonth) AS Year, AVG(TransactionAmount) AS AvgPrice FROM REALESTATEAPTALL GROUP BY Year ORDER BY Year;

---

1.2. 호가 대비 실거래가 비율

 

SELECT A.ComplexName, AVG(A.TransactionAmount) / AVG(CAST(B.MAXPRICE AS REAL)) * 100 AS RealToMarketRatio FROM REALESTATEAPTALL A JOIN ESTATENAVERALLINFOATTACH B ON A.CityCounty = B.CORTARADDRESS AND A.MainNumber = B.LNBRMNNM AND A.SubNumber = B.LNBRSLNO GROUP BY A.ComplexName;

---

1.3. 최대-최소 가격 범위 (단지별로)

 

SELECT A.ComplexName, MAX(A.TransactionAmount) - MIN(A.TransactionAmount) AS PriceRange FROM REALESTATEAPTALL A GROUP BY A.ComplexName;

---

🏢 2. 아파트별 상세 분석

2.1. 단지별 거래량

 

SELECT ComplexName, COUNT(*) AS TransactionCount FROM REALESTATEAPTALL GROUP BY ComplexName ORDER BY TransactionCount DESC;

---

2.2. 평형대별 평균가

 

SELECT ROUND(AreaSquareMeters / 3.3) AS Pyeong, AVG(TransactionAmount) AS AvgPrice FROM REALESTATEAPTALL GROUP BY Pyeong ORDER BY Pyeong;

---

2.3. 신축 vs 구축 가격 차이

 

SELECT CASE WHEN ConstructionYear >= 2015 THEN '신축' ELSE '구축' END AS BuildingAge, AVG(TransactionAmount) AS AvgPrice FROM REALESTATEAPTALL GROUP BY BuildingAge;

---

📈 3. 투자 가치 및 수익률 분석

3.1. 매매-전세 갭

 

SELECT A.ComplexName, AVG(A.TransactionAmount) - AVG(CAST(B.MAXLEASEPRICE AS REAL)) AS GapPrice FROM REALESTATEAPTALL A JOIN ESTATENAVERALLINFOATTACH B ON A.CityCounty = B.CORTARADDRESS AND A.MainNumber = B.LNBRMNNM AND A.SubNumber = B.LNBRSLNO GROUP BY A.ComplexName;

---

3.2. 전세가율

 

SELECT A.ComplexName, AVG(CAST(B.MAXLEASEPRICE AS REAL)) / AVG(CAST(B.MAXPRICE AS REAL)) * 100 AS LeaseRatio FROM ESTATENAVERALLINFOATTACH B JOIN REALESTATEAPTALL A ON A.CityCounty = B.CORTARADDRESS AND A.MainNumber = B.LNBRMNNM AND A.SubNumber = B.LNBRSLNO GROUP BY A.ComplexName;

---

📌 4. 지역별 비교 분석

4.1. 지역별 시세 격차

 

SELECT CityCounty, AVG(TransactionAmount) AS AvgPrice FROM REALESTATEAPTALL GROUP BY CityCounty ORDER BY AvgPrice DESC;

---

4.2. 인기 지역 분석 (거래량 많은 순)

 

SELECT CityCounty, COUNT(*) AS TransactionCount FROM REALESTATEAPTALL GROUP BY CityCounty ORDER BY TransactionCount DESC;

---

📊 5. 특이사항 및 추가 분석

5.1. 급매 vs 일반 매물 (평균 이하 가격 비율)

 

WITH AvgPrice AS ( SELECT ComplexName, AVG(TransactionAmount) AS AvgTransPrice FROM REALESTATEAPTALL GROUP BY ComplexName ) SELECT A.ComplexName, SUM(CASE WHEN A.TransactionAmount < B.AvgTransPrice THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS BelowAvgRatio FROM REALESTATEAPTALL A JOIN AvgPrice B ON A.ComplexName = B.ComplexName GROUP BY A.ComplexName;

---

5.2. 공시지가 대비 매매가
공시지가 테이블이 없다면 가정하고 작성한 쿼리입니다.

 

SELECT A.ComplexName, AVG(A.TransactionAmount) / AVG(B.OfficialPrice) * 100 AS PriceToOfficialRatio FROM REALESTATEAPTALL A JOIN OFFICIALPRICES B ON A.CityCounty = B.CityCounty AND A.MainNumber = B.MainNumber AND A.SubNumber = B.SubNumber GROUP BY A.ComplexName;