No description
No columns
SELECT prefcode AS prefecture_code, citycode AS city_code, CASE WHEN (regexp_full_match(citycode, '[0-9]{5}')) THEN ((citycode || CAST(((11 - ((((((6 * CAST(substr(citycode, 1, 1) AS INTEGER)) + (5 * CAST(substr(citycode, 2, 1) AS INTEGER))) + (4 * CAST(substr(citycode, 3, 1) AS INTEGER))) + (3 * CAST(substr(citycode, 4, 1) AS INTEGER))) + (2 * CAST(substr(citycode, 5, 1) AS INTEGER))) % 11)) % 10) AS VARCHAR))) ELSE NULL END AS lg_code, make_date(CAST("year" AS INTEGER), CAST("month" AS INTEGER), 1) AS date, CAST("year" AS INTEGER) AS "year", CAST("month" AS INTEGER) AS "month", dayflag, timezone, from_area, CASE WHEN ((dayflag = '0')) THEN ('休日') WHEN ((dayflag = '1')) THEN ('平日') WHEN ((dayflag = '2')) THEN ('全日') ELSE NULL END AS day_type, CASE WHEN ((timezone = '0')) THEN ('昼') WHEN ((timezone = '1')) THEN ('深夜') WHEN ((timezone = '2')) THEN ('終日') ELSE NULL END AS time_of_day, CASE WHEN ((from_area = '0')) THEN ('同一市区町村') WHEN ((from_area = '1')) THEN ('同一都道府県') WHEN ((from_area = '2')) THEN ('同一地方') WHEN ((from_area = '3')) THEN ('他の地方') ELSE NULL END AS origin, CAST(population AS INTEGER) AS population, (('monthly_fromto_city_' || regexp_extract(filename, '([0-9]{2})\.parquet$', 1)) || '.zip') AS source_zip FROM people_flow.raw_city_population_by_origin