공동주택 공시가격 데이터 (assessed_prices)

export TAX_CHAT_SUPABASE_DB_URL='postgresql://postgres.<ref>:****@aws-0-<region>.pooler.supabase.com:5432/postgres'
export CSV_PATH='/Users/<you>/workspace/howmuchhome-web/data/assessed-prices/국토교통부_주택 공시가격 정보(<YYYY>).csv'
 
# 접속 확인
psql "$TAX_CHAT_SUPABASE_DB_URL" -c "select pg_size_pretty(pg_database_size('postgres'));"

psql "$TAX_CHAT_SUPABASE_DB_URL" --set ON_ERROR_STOP=on <<'SQL'
drop table if exists public._stg_assessed_prices;
create unlogged table public._stg_assessed_prices (
  base_year            text, base_month         text,
  legal_dong_code      text, road_address       text,
  sido                 text, sigungu            text,
  eupmyeon             text, dongri             text,
  special_code         text, bonbun             text,
  bubun                text, special_name       text,
  complex_name         text, dong               text, ho   text,
  exclusive_area_sqm   text, assessed_price     text,
  complex_code         text, dong_code          text, ho_code text,
  building_register_pk text
);
SQL
 
psql "$TAX_CHAT_SUPABASE_DB_URL" -c "\\COPY public._stg_assessed_prices FROM '${CSV_PATH}' WITH (FORMAT csv, HEADER true, QUOTE '\"', ENCODING 'UTF8')"
 
psql "$TAX_CHAT_SUPABASE_DB_URL" -c "select count(*) as staging_rows from public._stg_assessed_prices;"

SIDOS=$(psql "$TAX_CHAT_SUPABASE_DB_URL" -tA \
  -c "select sido from public._stg_assessed_prices where sido <> '' group by sido order by sido;")
 
while IFS= read -r SIDO; do
  [ -z "$SIDO" ] && continue
  echo "→ $SIDO"
  psql "$TAX_CHAT_SUPABASE_DB_URL" --quiet --set ON_ERROR_STOP=on \
    -v sido_var="$SIDO" <<'SQL'
insert into public.assessed_prices_unit (
  year, base_month, legal_dong_code, sido, sigungu,
  complex_name, dong, ho,
  exclusive_area_sqm, assessed_price,
  road_address, building_register_pk
)
select
  yr, bm, legal_v, sido_v, sigungu_v, name_v, dong_v, ho_v,
  area_v, price_v, road_v, bpk_v
from (
  select distinct on (yr, sido_v, sigungu_v, name_v, dong_v, ho_v, area_v)
    yr, bm, legal_v, sido_v, sigungu_v, name_v, dong_v, ho_v,
    area_v, price_v, road_v, bpk_v
  from (
    select
      nullif(base_year, '')::smallint                       as yr,
      nullif(base_month, '')::smallint                      as bm,
      nullif(legal_dong_code, '')                           as legal_v,
      sido                                                  as sido_v,
      nullif(sigungu, '')                                   as sigungu_v,
      nullif(complex_name, '')                              as name_v,
      nullif(dong, '')                                      as dong_v,
      nullif(ho, '')                                        as ho_v,
      nullif(exclusive_area_sqm, '')::numeric(7,3)          as area_v,
      nullif(replace(assessed_price, ',', ''), '')::bigint  as price_v,
      nullif(road_address, '')                              as road_v,
      nullif(building_register_pk, '')                      as bpk_v
    from public._stg_assessed_prices
    where sido = :'sido_var'
      and assessed_price <> '' and complex_name <> '' and sigungu <> ''
  ) cast_data
  order by yr, sido_v, sigungu_v, name_v, dong_v, ho_v, area_v, bm desc nulls last
) dedup
on conflict (year, sido, sigungu, complex_name, dong, ho, exclusive_area_sqm)
do update set
  base_month           = excluded.base_month,
  legal_dong_code      = excluded.legal_dong_code,
  assessed_price       = excluded.assessed_price,
  road_address         = excluded.road_address,
  building_register_pk = excluded.building_register_pk;
SQL
done <<< "$SIDOS"
 
psql "$TAX_CHAT_SUPABASE_DB_URL" -c "analyze public.assessed_prices_unit;"

SIDOS=$(psql "$TAX_CHAT_SUPABASE_DB_URL" -tA \
  -c "select distinct sido from public.assessed_prices_unit where year = (select max(year) from public.assessed_prices_unit) order by sido;")
 
while IFS= read -r SIDO; do
  [ -z "$SIDO" ] && continue
  echo "→ $SIDO"
  psql "$TAX_CHAT_SUPABASE_DB_URL" --quiet --set ON_ERROR_STOP=on \
    -v sido_var="$SIDO" <<'SQL'
with target_year as (
  select max(year) as y from public.assessed_prices_unit
)
insert into public.assessed_prices_area_avg (
  year, sido, sigungu, complex_name, exclusive_area_sqm_bucket,
  avg_assessed_price, median_assessed_price,
  min_assessed_price, max_assessed_price, unit_count
)
select
  u.year, u.sido, u.sigungu, u.complex_name,
  round(u.exclusive_area_sqm, 1)                                            as exclusive_area_sqm_bucket,
  round(avg(u.assessed_price))::bigint                                      as avg_assessed_price,
  percentile_cont(0.5) within group (order by u.assessed_price)::bigint     as median_assessed_price,
  min(u.assessed_price)                                                     as min_assessed_price,
  max(u.assessed_price)                                                     as max_assessed_price,
  count(*)                                                                  as unit_count
from public.assessed_prices_unit u
cross join target_year t
where u.year = t.y and u.sido = :'sido_var'
group by u.year, u.sido, u.sigungu, u.complex_name, round(u.exclusive_area_sqm, 1)
on conflict (year, sido, sigungu, complex_name, exclusive_area_sqm_bucket)
do update set
  avg_assessed_price    = excluded.avg_assessed_price,
  median_assessed_price = excluded.median_assessed_price,
  min_assessed_price    = excluded.min_assessed_price,
  max_assessed_price    = excluded.max_assessed_price,
  unit_count            = excluded.unit_count;
SQL
done <<< "$SIDOS"
 
psql "$TAX_CHAT_SUPABASE_DB_URL" -c "analyze public.assessed_prices_area_avg;"

psql "$TAX_CHAT_SUPABASE_DB_URL" <<'SQL'
drop table if exists public._stg_assessed_prices;
 
select year, count(distinct complex_name) as complex_count, count(*) as unit_count
  from public.assessed_prices_unit
  group by year order by year desc;
 
select pg_size_pretty(pg_database_size('postgres')) as db_size;
SQL

psql "$TAX_CHAT_SUPABASE_DB_URL" -c "ALTER ROLE postgres RESET statement_timeout;"
psql "$TAX_CHAT_SUPABASE_DB_URL" -c "ALTER ROLE postgres RESET idle_in_transaction_session_timeout;"

-- 연도별 단지/호수 확인
select year, count(distinct complex_name) as complex_count, count(*) as unit_count
  from public.assessed_prices_unit
  group by year order by year desc;
 
-- 단지명 검색 (trigram 인덱스 활용)
select complex_name, dong, ho, exclusive_area_sqm, assessed_price
  from public.assessed_prices_unit
  where year = 2025 and complex_name ilike '%반포자이%'
  limit 10;
 
-- 평형 평균 확인
select complex_name, exclusive_area_sqm_bucket,
       avg_assessed_price, median_assessed_price, unit_count
  from public.assessed_prices_area_avg
  where year = 2025 and complex_name ilike '%반포자이%'
  order by exclusive_area_sqm_bucket;
 
-- DB 사이즈 확인
select pg_size_pretty(pg_database_size('postgres'));

delete from public.assessed_prices_unit     where year = 2025;
delete from public.assessed_prices_area_avg where year = 2025;