adityawarmanfw / sqlmesh-lhkpn Goto Github PK

duckdb> WITH nik_daerah AS (
   ...>   SELECT 'abcd' AS id_data, 1 AS nik, 'JAWA TIMUR' AS nama_provinsi UNION 
   ...>   SELECT 'def' AS id_data, 2 AS nik, 'JAWA TENGAH' AS nama_provinsi
   ...> ), nik_id_data AS (
   ...>   SELECT 'abcd' AS id_data, 'abcd' AS id_lhkpn, 'abc' AS id_lhkpn_sebelumnya UNION
   ...>   SELECT 'abcd' AS id_data, 'abc' AS id_lhkpn, 'ab' AS id_lhkpn_sebelumnya UNION 
   ...>   SELECT 'ab' AS id_data, 'ab' AS id_lhkpn, 'a' AS id_lhkpn_sebelumnya UNION
   ...>   SELECT 'def' AS id_data, 'defg' AS id_lhkpn, 'de' AS id_lhkpn_sebelumnya
   ...> )
   ...> SELECT nik, nama_provinsi, id_data, id_lhkpn, id_lhkpn_sebelumnya
   ...>   FROM stg 
   ...>   LEFT JOIN nik_daerah USING (id_data);
┌─────┬───────────────┬─────────┬──────────┬─────────────────────┐
│ nik ┆ nama_provinsi ┆ id_data ┆ id_lhkpn ┆ id_lhkpn_sebelumnya │
╞═════╪═══════════════╪═════════╪══════════╪═════════════════════╡
│   2 ┆ JAWA TENGAH   ┆ def     ┆ defg     ┆ de                  │
│   1 ┆ JAWA TIMUR    ┆ abcd    ┆ abcd     ┆ abc                 │
│   1 ┆ JAWA TIMUR    ┆ abcd    ┆ abc      ┆ ab                  │
│     ┆               ┆ ab      ┆ ab       ┆ a                   │
└─────┴───────────────┴─────────┴──────────┴─────────────────────┘
Elapsed: 15 ms

duckdb> WITH nik_daerah AS (
   ...>   SELECT 'abcd' AS id_data, 1 AS nik, 'JAWA TIMUR' AS nama_provinsi UNION 
   ...>   SELECT 'def' AS id_data, 2 AS nik, 'JAWA TENGAH' AS nama_provinsi
   ...> ), nik_id_data AS (
   ...>   SELECT 'abcd' AS id_data, 'abcd' AS id_lhkpn, 'abc' AS id_lhkpn_sebelumnya UNION
   ...>   SELECT 'abcd' AS id_data, 'abc' AS id_lhkpn, 'ab' AS id_lhkpn_sebelumnya UNION 
   ...>   SELECT 'ab' AS id_data, 'ab' AS id_lhkpn, 'a' AS id_lhkpn_sebelumnya UNION
   ...>   SELECT 'def' AS id_data, 'defg' AS id_lhkpn, 'de' AS id_lhkpn_sebelumnya
   ...> ), nik_id_data AS (
   ...>   SELECT nik, nama_provinsi, id_data, id_lhkpn, id_lhkpn_sebelumnya
   ...>   FROM stg 
   ...>   LEFT JOIN nik_daerah USING (id_data)
   ...> ), nik_id_data_diratakan AS (
   ...>   SELECT nik, nama_provinsi, id_data FROM nik_id_data UNION
   ...>   SELECT nik, nama_provinsi, id_lhkpn FROM nik_id_data UNION
   ...>   SELECT nik, nama_provinsi, id_lhkpn_sebelumnya FROM nik_id_data
   ...> )
   ...> SELECT * FROM nik_id_data_diratakan ORDER BY id_data;
┌─────┬───────────────┬─────────┐
│ nik ┆ nama_provinsi ┆ id_data │
╞═════╪═══════════════╪═════════╡
│     ┆               ┆ a       │
│   1 ┆ JAWA TIMUR    ┆ ab      │
│     ┆               ┆ ab      │
│   1 ┆ JAWA TIMUR    ┆ abc     │
│   1 ┆ JAWA TIMUR    ┆ abcd    │
│   2 ┆ JAWA TENGAH   ┆ de      │
│   2 ┆ JAWA TENGAH   ┆ def     │
│   2 ┆ JAWA TENGAH   ┆ defg    │
└─────┴───────────────┴─────────┘
Elapsed: 21 ms