Bersihin Data Dalam Database Pakai SQL, Panduan Cleansing Data ASN di Pemerintahan

Analisis data berbagai macam jenisnya. Mulai dari yang paling sederhana seperti perhitungan statistik deskriptif (mean, median, modus, dan mungkin juga nilai minimum dan maksimum), pembuatan visualisasi data, hingga analisis data yang lebih kompleks seperti korelasi, regresi, hingga prediksi. Ett, jangan buru-buru.

Sebelum melakukan itu semua, hal yang sering dilupakan atau bahkan 'sengaja' dilupakan orang karena butuh 'ketelatenan' dan 'ketelitian'. Tahapan tersebut adalah data preprocessing, yakni tahapan untuk mengubah data mentah menjadi data siap olah. Data mentah seringkali bentuknya kurang teratur, tidak tervalidasi, dan banyak nilai kosong (NULL) ataupun kolom yang tidak sesuai. Bahkan, hampir 80 hingga 90 persen waktu yang dibutuhkan oleh data scientist adalah pada tahapan data cleansing.

Tulisan ini akan membahas beberapa syntax SQL yang saya gunakan dalam pengolahan data ASN dan kependudukan. Tujuan utamanya sih sebagai direktori agar nantinya ketika dibutuhkan lagi, saya hanya tinggal membuka tulisan ini saja. Sebagai informasi, saya menggunakan PostgreSQL dengan database manager DBeaver. Selamat menyimak.

⚠️ WARNING: Lakukan dengan teliti dan hati-hati karena perintah UPDATE — SET dapat mengubah isi database. Sebelum menjalankan perintah SQL, sebaiknya periksa kembali apa yang mau diperbaiki.

#1 Cleansing Data Jenis Kelamin

Demografi penting dalam banyak analisis data kependudukan adalah jenis kelamin. Perbedaan demografi antara laki-laki dan perempuan biasanya akan mempengaruhi banyak variabel.

Mengetahui Nilai Unik dan Jumlahnya (SELECT — GROUP BY)

Untuk melakukan cleansing terhadap variabel jenis kelamin, langkah pertama, kita harus mengecek terlebih dahulu nilai-nilai apa saja (unique value) yang terdapat dalam kolom 'jenis_kelamin' beserta jumlahnya. Untuk mengeceknya, kita bisa menggunakan perintah SELECT — GROUP BY.

SELECT jenis_kelamin, count(*) as jumlah
  FROM pegawai_asn
  GROUP BY jenis_kelamin
  ORDER BY jumlah DESC;

Syntax SQL tersebut misalnya akan menghasilkan tabel sebagai berikut:

jenis_kelamin	jumlah
male	75
female	53

Kita juga dapat menggunakan operator LIKE untuk mencari frasa yang menyerupai.

Menghapus Spasi di depan dan di belakang String (TRIM)

UPDATE table_name
  SET column_name = TRIM(column_name);

Ketika ada karakter mencurigakan, misalnya ENTER atau TAB, perlu memasukkan query TRIM seperti berikut:

UPDATE table_name
  SET column_name = TRIM('Â' FROM column_name);

Pengecekan Ulang dan Set-Update

⚠️ Catatan: Berhati-hatilah ketika melakukan perintah SET — UPDATE, pastikan (double check) dengan menggunakan perintah SELECT — GROUP BY. Contohnya, ketika kita akan memperbaiki 'male' pada kolom jenis_kelamin menjadi 'Laki-laki', untuk memastikan yang ingin kita ubah adalah 'male' tanpa mengandung female, sebagai berikut.

Pertama, sebelum mengupdate data kita perlu cek dan ricek data yang akan kita update dengan query SELECT-WHERE seperti berikut:

❌ Berikut contoh yang salah, karena akan menghasilkan nilai yang mengandung male, yakni: 'male' dan 'female'

SELECT jenis_kelamin, count(*) as jumlah
  FROM pegawai_asn
  WHERE LOWER(jenis_kelamin) LIKE '%male%'
  GROUP BY jenis_kelamin
  ORDER BY jumlah DESC;

✅ Berikut syntax yang benar untuk menghasilkan hanya nilai yang mengandung male

SELECT jenis_kelamin, count(*) as jumlah
  FROM pegawai_asn
  WHERE LOWER(jenis_kelamin) = 'male'
  GROUP BY jenis_kelamin
  ORDER BY jumlah DESC;

🔁 Berdasarkan hasil pengecekan, maka update dengan syntax berikut

UPDATE pegawai_asn
  SET jenis_kelamin = 'Laki-laki'
  WHERE LOWER(jenis_kelamin) = 'male';

Cleansing untuk Jenis Kelamin Kosong

Masalah cleansing untuk variabel jenis kelamin tidak hanya berhenti sampai sini saja. Selain harus mengidentifikasi mana yang Laki-laki, misalnya 'pria', 'male', 'm', 'cowok', 'laki2', dsb, ataupun mengidentifikasi Perempuan, misalnya 'wanita', 'female', 'f', 'cewek', 'pr', dsb. Lakukan query yang serupa di atas untuk mengubah nama suatu jenis kelamin ke jenis kelamin lainnya.perhatikan dalam menggunakan = atau LIKE.

Terkadang terdapat pula dalam tabel database yang isiannya kosong, baik itu empty string ('') atau null (NULL). Untuk mengatasi hal-hal semacam ini, kita tentu saja harus mengecek atribut lain yang melekat pada pelaku, misalnya nomor identitas. 2 (dua) nomor identitas yang sering digunakan dalam basis data ASN adalah NIK (nomor induk kependudukan), dan NIP (nomor induk pegawai). Untuk handling data jenis kelamin NULL dengan NIP, kita akan mempelajarinya di bagian berikutnya.

#2 Cleansing Data NIP (Nomor Induk Pegawai)

Struktur NIP ASN

NIP ASN terdiri dari 18 digit dengan struktur sebagai berikut:

YYYYMMDD YYYYMM J UUU

8 digit pertama (YYYYMMDD): Tanggal lahir dalam format Tahun-Bulan-Tanggal
6 digit selanjutnya (YYYYMM):
- Untuk PNS: Tanggal pengangkatan CPNS dalam format Tahun-Bulan
- Untuk PPPK: Tahun pengangkatan + frekuensi pengangkatan (21 = pertama kali, 22 = kedua kali, dst)
1 digit selanjutnya (J): Jenis kelamin (1 = Laki-laki, 2 = Perempuan)
3 digit terakhir (UUU): Nomor urut

Preprocessing NIP

Adakalanya, atribut nomor identitas seperti NIK atau NIP belum 'clean'. Terkadang masih ada spasi dan tanda baca ataupun huruf di mana-mana. Hal tersebut terjadi antara lain karena kesalahan mesin (pembaca teks otomatis), kesalahan manusia, hingga tidak adanya validasi sistem.

1. Menghilangkan spasi dan karakter non-angka dalam NIP

Menghilangkan spasi di antara angka-angka dalam NIP, alih-alih menuliskannya tanpa spasi, beberapa pegawai seringkali menginput ke dalam sistem menggunakan spasi. Misalnya: 'XXXXXXXX XXXXXX X XXX'

UPDATE pegawai_asn
  SET nip = REPLACE(nip, ' ', '');

Menghilangkan karakter selain angka (hanya menyisakan 0-9)

UPDATE pegawai_asn
  SET nip = REGEXP_REPLACE(nip, '[^0-9]', '', 'g');

2. Validasi panjang NIP

Seperti yang kita tahu, NIP atau NIPPPK yang valid memiliki panjang 18 digit. Berikut adalah cara untuk cek jumlah record berdasarkan panjang NIP.

SELECT LENGTH(nip) as panjang_nip, COUNT(*) as jumlah
  FROM pegawai_asn
  WHERE nip IS NOT NULL AND nip != ''
  GROUP BY LENGTH(nip)
  ORDER BY jumlah DESC;

Berikut untuk menandai NIP yang tidak valid (bukan 18 digit), nah dari sini kita bisa langsung update manual melalui tabel layaknya Excel dengan menggunakan fitur yang disediakan database manager seperti DBeaver atau Navicat.

SELECT nip, LENGTH(nip) as panjang
  FROM pegawai_asn
  WHERE LENGTH(nip) != 18 AND nip IS NOT NULL;

Ekstraksi Informasi dari NIP

1. Mengecek dan Memperbaiki Jenis Kelamin dari NIP

Mengecek distribusi pegawai menurut jenis kelamin berdasarkan digit ke-15 NIP

SELECT
  CASE
    WHEN SUBSTRING(nip, 15, 1) = '1' THEN 'LAKI-LAKI'
    WHEN SUBSTRING(nip, 15, 1) = '2' THEN 'PEREMPUAN'
    ELSE 'TIDAK VALID'
  END as jenis_kelamin_nip,
  COUNT(*) AS jumlah
FROM pegawai_asn
WHERE LENGTH(nip) = 18
GROUP BY SUBSTRING(nip, 15, 1)
ORDER BY jumlah DESC;

Untuk mengatasi field jenis kelamin NULL kita bisa memanfaatkan informasi pada NIP. Berikut contoh query yang dapat digunakan untuk update jenis kelamin yang kosong berdasarkan NIP

UPDATE pegawai_asn
  SET jenis_kelamin = 'LAKI-LAKI'
  WHERE (jenis_kelamin IS NULL OR jenis_kelamin = '')
    AND LENGTH(nip) = 18
    AND SUBSTRING(nip, 15, 1) = '1';

UPDATE pegawai_asn
  SET jenis_kelamin = 'PEREMPUAN'
  WHERE (jenis_kelamin IS NULL OR jenis_kelamin = '')
    AND LENGTH(nip) = 18
    AND SUBSTRING(nip, 15, 1) = '2';

2. Mengecek Tanggal Lahir dari NIP

Ekstraksi tanggal lahir dari 8 digit pertama NIP, tanggal lahir dapat melahirkan atribut baru seperti usia, generasi usia, dan prediksi pensiun

SELECT 
  nip,
  SUBSTRING(nip, 1, 8) as tgl_lahir_nip,
  TO_DATE(SUBSTRING(nip, 1, 8), 'YYYYMMDD') as tanggal_lahir,
  EXTRACT(YEAR FROM AGE(TO_DATE(SUBSTRING(nip, 1, 8), 'YYYYMMDD'))) as usia
FROM pegawai_asn
WHERE LENGTH(nip) = 18
  AND SUBSTRING(nip, 1, 8) ~ '^[0-9]{8}$'
LIMIT 10;

Validasi tanggal lahir yang tidak masuk akal

SELECT nip, SUBSTRING(nip, 1, 8) as tgl_lahir_nip
FROM pegawai_asn
WHERE LENGTH(nip) = 18
  AND (
    SUBSTRING(nip, 1, 4)::INTEGER < 1940 OR  -- Lahir sebelum 1940
    SUBSTRING(nip, 1, 4)::INTEGER > 2010 OR  -- Lahir setelah 2010
    SUBSTRING(nip, 5, 2)::INTEGER < 1 OR     -- Bulan < 1
    SUBSTRING(nip, 5, 2)::INTEGER > 12 OR    -- Bulan > 12
    SUBSTRING(nip, 7, 2)::INTEGER < 1 OR     -- Tanggal < 1
    SUBSTRING(nip, 7, 2)::INTEGER > 31       -- Tanggal > 31
  );

3. Mengecek Tanggal Pengangkatan CPNS/PPPK

Ekstraksi informasi pengangkatan CPNS/PPPK dari digit 9-14

SELECT 
  nip,
  SUBSTRING(nip, 9, 6) as pengangkatan_info,
  SUBSTRING(nip, 9, 4) as tahun_pengangkatan,
  SUBSTRING(nip, 13, 2) as bulan_atau_frekuensi,
  CASE 
    WHEN SUBSTRING(nip, 13, 2)::INTEGER BETWEEN 1 AND 12 THEN 'PNS'
    WHEN SUBSTRING(nip, 13, 2)::INTEGER BETWEEN 21 AND 99 THEN 'PPPK'
    ELSE 'TIDAK DIKETAHUI'
  END as status_kepegawaian,
  CASE 
    WHEN SUBSTRING(nip, 13, 2)::INTEGER BETWEEN 21 AND 99 
    THEN SUBSTRING(nip, 13, 2)::INTEGER - 20
    ELSE NULL
  END as frekuensi_pengangkatan_pppk
FROM pegawai_asn
WHERE LENGTH(nip) = 18
LIMIT 10;

Update status kepegawaian (PNS/PPPK) berdasarkan NIP

UPDATE pegawai_asn
  SET status_kepegawaian = CASE 
    WHEN SUBSTRING(nip, 13, 2)::INTEGER BETWEEN 1 AND 12 THEN 'PNS'
    WHEN SUBSTRING(nip, 13, 2)::INTEGER BETWEEN 21 AND 99 THEN 'PPPK'
    ELSE status_kepegawaian
  END
WHERE LENGTH(nip) = 18 
  AND (status_kepegawaian IS NULL OR status_kepegawaian = '');

4. Validasi Konsistensi Data

Mengecek konsistensi jenis kelamin antara kolom dan NIP

SELECT 
  COUNT(*) as total_tidak_konsisten
FROM pegawai_asn
WHERE LENGTH(nip) = 18
  AND (
    (LOWER(jenis_kelamin) IN ('laki-laki', 'pria', 'male', 'l', 'm') AND SUBSTRING(nip, 15, 1) = '2') OR
    (LOWER(jenis_kelamin) IN ('perempuan', 'wanita', 'female', 'p', 'f') AND SUBSTRING(nip, 15, 1) = '1')
  );

Menampilkan data yang tidak konsisten untuk review manual

SELECT nip, jenis_kelamin, SUBSTRING(nip, 15, 1) as digit_jk_nip
FROM pegawai_asn
WHERE LENGTH(nip) = 18
  AND (
    (LOWER(jenis_kelamin) IN ('laki-laki', 'pria', 'male', 'l', 'm') AND SUBSTRING(nip, 15, 1) = '2') OR
    (LOWER(jenis_kelamin) IN ('perempuan', 'wanita', 'female', 'p', 'f') AND SUBSTRING(nip, 15, 1) = '1')
  );

Contoh Query Komprehensif untuk Analisis NIP

SELECT 
  nip,
  SUBSTRING(nip, 1, 8) as tgl_lahir_raw,
  TO_DATE(SUBSTRING(nip, 1, 8), 'YYYYMMDD') as tanggal_lahir,
  EXTRACT(YEAR FROM AGE(TO_DATE(SUBSTRING(nip, 1, 8), 'YYYYMMDD'))) as usia,
  SUBSTRING(nip, 9, 4) as tahun_pengangkatan,
  SUBSTRING(nip, 13, 2) as bulan_atau_frekuensi,
  CASE 
    WHEN SUBSTRING(nip, 13, 2)::INTEGER BETWEEN 1 AND 12 THEN 'PNS'
    WHEN SUBSTRING(nip, 13, 2)::INTEGER BETWEEN 21 AND 99 THEN 'PPPK'
    ELSE 'TIDAK VALID'
  END as status_kepegawaian,
  SUBSTRING(nip, 15, 1) as kode_jk,
  CASE
    WHEN SUBSTRING(nip, 15, 1) = '1' THEN 'LAKI-LAKI'
    WHEN SUBSTRING(nip, 15, 1) = '2' THEN 'PEREMPUAN'
    ELSE 'TIDAK VALID'
  END as jenis_kelamin_nip,
  SUBSTRING(nip, 16, 3) as nomor_urut,
  CASE 
    WHEN LENGTH(nip) != 18 THEN 'PANJANG TIDAK VALID'
    WHEN SUBSTRING(nip, 1, 4)::INTEGER < 1940 OR SUBSTRING(nip, 1, 4)::INTEGER > 2010 THEN 'TAHUN LAHIR TIDAK VALID'
    WHEN SUBSTRING(nip, 5, 2)::INTEGER < 1 OR SUBSTRING(nip, 5, 2)::INTEGER > 12 THEN 'BULAN LAHIR TIDAK VALID'
    WHEN SUBSTRING(nip, 7, 2)::INTEGER < 1 OR SUBSTRING(nip, 7, 2)::INTEGER > 31 THEN 'TANGGAL LAHIR TIDAK VALID'
    WHEN SUBSTRING(nip, 15, 1) NOT IN ('1', '2') THEN 'KODE JENIS KELAMIN TIDAK VALID'
    ELSE 'VALID'
  END as status_validasi
FROM pegawai_asn
WHERE nip ~ '^[0-9]{18}$'  -- Hanya NIP yang terdiri dari 18 digit
ORDER BY nip;

#3 Cleansing Data Asal Instansi/Unit Kerja/Satuan Kerja

Mengganti/Substitusi sebagian String (REPLACE)

Berikut rumus umum untuk mengganti/menghilangkan kata 'text' dalam string

UPDATE table_name
  SET column_name = REPLACE(column_name, 'text', '');

Contoh 1: Mengganti kata PEMKAB menjadi PEMERINTAH KAB

UPDATE table_name
  SET column_name = REPLACE(column_name, 'PEMKAB', 'PEMERINTAH KAB');

Contoh 2: Standardisasi nama instansi yang sering salah tulis

```sql
UPDATE pegawai_asn
  SET nama_instansi = REPLACE(REPLACE(REPLACE(nama_instansi, 
    'PEMDA', 'PEMERINTAH DAERAH'), 
    'PEMKOT', 'PEMERINTAH KOTA'),
    'PEMKAB', 'PEMERINTAH KABUPATEN');

Mengganti Nilai Sel Keseluruhan dengan Kondisi Tertentu (WHERE)

UPDATE table_name
  SET column_name = 'BADAN KEPENDUDUKAN DAN KELUARGA BERENCANA NASIONAL'
  WHERE column_name = 'BKKBN';

Mengganti sel bernilai ' '(kosong) menjadi NULL

UPDATE table_name
  SET column_name = NULL
  WHERE column_name = '' OR column_name = ' ';

Standardisasi nama kementerian

```sql
UPDATE pegawai_asn
  SET nama_instansi = CASE
    WHEN UPPER(nama_instansi) LIKE '%KEMENDAGRI%' OR UPPER(nama_instansi) LIKE '%KEMENTERIAN DALAM NEGERI%' 
      THEN 'KEMENTERIAN DALAM NEGERI'
    WHEN UPPER(nama_instansi) LIKE '%KEMENKEU%' OR UPPER(nama_instansi) LIKE '%KEMENTERIAN KEUANGAN%' 
      THEN 'KEMENTERIAN KEUANGAN'
    WHEN UPPER(nama_instansi) LIKE '%BPS%' OR UPPER(nama_instansi) LIKE '%BADAN PUSAT STATISTIK%' 
      THEN 'BADAN PUSAT STATISTIK'
    ELSE nama_instansi
  END
WHERE nama_instansi IS NOT NULL;

Langkah Pencegahan

Mencegah lebih baik dari mengobati. Beberapa langkah pencegahan yang bisa dilakukan:

1. Validasi Input di Level Aplikasi

Membuat constraint untuk memastikan NIP hanya berisi 18 digit

ALTER TABLE pegawai_asn 
ADD CONSTRAINT check_nip_length 
CHECK (LENGTH(nip) = 18 AND nip ~ '^[0-9]{18}$');

Membuat constraint untuk jenis kelamin

ALTER TABLE pegawai_asn 
ADD CONSTRAINT check_jenis_kelamin 
CHECK (jenis_kelamin IN ('LAKI-LAKI', 'PEREMPUAN'));

2. Trigger untuk Auto-correction

Trigger untuk otomatis membersihkan NIP saat insert/update

CREATE OR REPLACE FUNCTION clean_nip()
RETURNS TRIGGER AS $$
BEGIN
  NEW.nip = REGEXP_REPLACE(NEW.nip, '[^0-9]', '', 'g');
  IF (NEW.jenis_kelamin IS NULL OR NEW.jenis_kelamin = '') AND LENGTH(NEW.nip) = 18 THEN
    NEW.jenis_kelamin = CASE 
      WHEN SUBSTRING(NEW.nip, 15, 1) = '1' THEN 'LAKI-LAKI'
      WHEN SUBSTRING(NEW.nip, 15, 1) = '2' THEN 'PEREMPUAN'
      ELSE NEW.jenis_kelamin
    END;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_clean_nip
  BEFORE INSERT OR UPDATE ON pegawai_asn
  FOR EACH ROW
  EXECUTE FUNCTION clean_nip();

3. Monitoring dan Alerting

Query untuk monitoring kualitas data secara berkala, query ini bisa disimpan dalam view atau materialized view untuk pengecekan berkala.

SELECT 
  'NIP Tidak Valid' as kategori,
  COUNT(*) as jumlah
FROM pegawai_asn 
WHERE LENGTH(nip) != 18 OR nip !~ '^[0-9]{18}$'
UNION ALL
SELECT 
  'Jenis Kelamin Kosong' as kategori,
  COUNT(*) as jumlah
FROM pegawai_asn 
WHERE jenis_kelamin IS NULL OR jenis_kelamin = ''
UNION ALL
SELECT 
  'Inkonsistensi Jenis Kelamin' as kategori,
  COUNT(*) as jumlah
FROM pegawai_asn
WHERE LENGTH(nip) = 18
  AND (
    (LOWER(jenis_kelamin) IN ('laki-laki', 'pria', 'male') AND SUBSTRING(nip, 15, 1) = '2') OR
    (LOWER(jenis_kelamin) IN ('perempuan', 'wanita', 'female') AND SUBSTRING(nip, 15, 1) = '1')
  );

3. Penggunaan API yang telah distandarkan

Badan Kepegawaian Negara (BKN) atau instansi yang bertanggung jawab kepada data kepegawaian nasional seharusnya sudah dapat menyediakan API yang dapat mengembalikan atribut demografi dasar seperti Nama, Jenis Kelamin, Instansi, dsb. hanya dengan bermodalkan NIP.

Kesimpulan

Data cleansing adalah tahapan krusial dalam analisis data, terutama untuk data pemerintahan yang sering kali memiliki inkonsistensi format dan nilai. Dengan menggunakan SQL secara sistematis, kita dapat:

Mengidentifikasi masalah data dengan query eksplorasi
Membersihkan data menggunakan fungsi string dan kondisi
Memvalidasi konsistensi data antar kolom
Mencegah masalah di masa depan dengan constraint dan trigger

Ingat selalu untuk melakukan backup data sebelum menjalankan operasi UPDATE dan selalu test query pada sample data terlebih dahulu sebelum menjalankan pada dataset lengkap.