sql memo - digital-go-jp/abr-geocoder GitHub Wiki

• townテーブルからkoaza_nameが空でないレコードを抽出。

SELECT lg_code,town_id,pref_name,county_name,city_name,od_city_name,oaza_town_name,koaza_name FROM town WHERE koaza_name !='';

• townテーブルからkoaza_nameが空でないレコードを選択し、lg_codeごとにグループ分けして、各グループ内で最初のレコードのみを抽出。

SELECT 
  pref_name,
  county_name,
  city_name,
  od_city_name,
  oaza_town_name,
  chome_name,
  koaza_name 
FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY lg_code ORDER BY town_id) as row_num
  FROM 
    town 
  WHERE 
    koaza_name != ''
) as subquery
WHERE 
  row_num = 1;

• 小字ユニーク抽出

SELECT DISTINCT koaza FROM town WHERE city_key="3819206177" AND koaza <> '' ORDER BY koaza

• rsdt全件
  • 結果: 22,501,162 行が 36265ms で返されました

SELECT DISTINCT
    city.pref,
    city.city,
    city.ward,
    rsdtdsp_rsdt.oaza_cho,
    rsdtdsp_rsdt.chome,
    rsdtdsp_rsdt.koaza,
    rsdtdsp_rsdt.blk_num,
    CASE 
        WHEN rsdtdsp_rsdt.rsdt_num IS NOT NULL AND rsdtdsp_rsdt.rsdt_num <> '' 
        THEN '-' || rsdtdsp_rsdt.rsdt_num
        ELSE rsdtdsp_rsdt.rsdt_num
    END AS rsdt_num,
    CASE 
        WHEN rsdtdsp_rsdt.rsdt_num2 IS NOT NULL AND rsdtdsp_rsdt.rsdt_num2 <> '' 
        THEN '-' || rsdtdsp_rsdt.rsdt_num2
        ELSE rsdtdsp_rsdt.rsdt_num2
    END AS rsdt_num2
FROM 
    rsdtdsp_rsdt
JOIN 
    city
ON 
    rsdtdsp_rsdt.lg_code = city.lg_code;

• 地番全件
  • 結果: 202,881,884 行が 744804ms で返されました

SELECT DISTINCT
    city.pref,
    city.city,
    city.ward,
    parcel.oaza_cho,
    parcel.chome,
    parcel.koaza,
    parcel.prc_num1,
    CASE 
        WHEN parcel.prc_num2 IS NOT NULL AND parcel.prc_num2 <> '' 
        THEN '-' || parcel.prc_num2
        ELSE parcel.prc_num2
    END AS prc_num2,
    CASE 
        WHEN parcel.prc_num3 IS NOT NULL AND parcel.prc_num3 <> '' 
        THEN '-' || parcel.prc_num3
        ELSE parcel.prc_num3
    END AS prc_num3
FROM 
    parcel
JOIN 
    city
ON 
    parcel.lg_code = city.lg_code;

• pref, county, city, ward, oaza_cho, chome, koazaを結合

SELECT 
    p.pref || 
    COALESCE(c.county, '') ||
    c.city ||
    COALESCE(c.ward, '') ||
    COALESCE(t.oaza_cho, '') ||
    COALESCE(t.chome, '') ||
    COALESCE(t.koaza, '') || 
    ' /*' || p.pref_key || ',' || c.city_key || ',' || t.town_key || '*/' 
    AS full_address
FROM town t
JOIN city c ON t.city_key = c.city_key
JOIN pref p ON c.pref_key = p.pref_key
ORDER BY c.lg_code, t.machiaza_id;