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;
