[SQL Tips] วิธีสร้างเทเบิลแบบขึ้นจากศูนย์ (hardcoded table) สำหรับเทสตามใจฉันบน Big Query/ SQLite/pandas

by GleeGM · April 30, 2024

hardcoded table SQL

รวมโค้ดที่ไว้สร้างเทเบิล hardcoded ไว้ในนี้แล้วววว
ภาพ: End of the season Sun, 1884 – William Merritt Chase

สารบัญ

สร้างเทเบิลจากศูนย์ (Hardcoded table) คืออะไรทำไปทำไม?

เวลาเราเขียนคิวรี่ต่างๆ ข้อมูลที่มีให้ทดสอบในเทเบิลจริงๆ ว่าสูตรที่เราเขียนดักไว้จะทำงานถูกต้องจริง ๆ มั้ย ในหลายๆครั้งมันก็ไม่มีให้

ทำให้เราต้อง mock ข้อมูลขึ้นมา นั่นคือขึ้นข้อมูลเทสที่ไม่มีอยู่ในเทเบิลจริง แต่ลักษณะหน้าตาข้อมูลเทสนี้จะเป็นตัวชี้วัดได้อย่างดีว่าโค้ดของเราทำงานถูกต้องมั้ย เอาไปใช้ต่อจริงได้รึเปล่า

สมมุติด้วยตัวอย่างให้เห็นภาพง่ายๆ ว่าเราเขียน CASE WHEN เพื่อ REPLACE() คอลัมน์นั้นเมื่อเจอ value ที่ลงท้ายด้วย ‘y’ แต่กลายเป็นว่าในเทเบิลเราไม่มีข้อมูลที่ลงท้ายด้วย y เลย เอาล่ะสิ ไอ้เราก็ว้าวุ่นซะด้วย จะใช้โค้ดนี้รันในงาน Production ก็กระไรอยู่ (แต่ตัวอย่างนี้ก็ง่ายเกิน 5555)

เนื่องจากเรามีเคสที่ต้องขึ้นข้อมูลเทสแบบนี้หลายครั้ง ค้นหาทีแล้วก็ลืม เลยใส่รวมไว้ในนี้แหละ ไว้เป็น Reference 55555 ของ Product ที่เราใช้ประจำ ถ้าอันไหนรู้สึกว่าใช้บ่อยหรือต้องมีเพิ่ม จะมาเพิ่มนะ

1.โค้ดสร้าง Hardcoded Table บน SQL วิธี UNION ALL (ใช้ได้แทบทุกยี่ห้อ)

วิธีนี้ลองแล้วใช้ได้ทั้งใน Big Query, PostgreSQL และ SQLite กับยี่ห้ออื่นก็น่าจะใช้ได้นะ

WITH example_table AS (
  SELECT '11/11/2011 9:05' as dt_date, 10 as sales, 8 as quantity
  UNION ALL
  SELECT '4/8/2011 14:17',45,1
)
SELECT *
FROM example_table ;




WITH dataset_mary AS (
  SELECT 'mary' AS name
  union all
  select 'แมรี่ mary' 
)

SELECT *,
FROM dataset_mary ;

2.โค้ดสร้าง Hardcoded Table บน SQLite วิธี VALUES

อีกวิธีเขียนที่ใช้ได้กับ SQLite แต่แนะนำให้ใช้วิธีที่ 1 ดีกว่า เนื่องจากมันแมสกว่า

WITH 
invoice_table as (
  select  Column1 as invoice_no,Column2 as itemcode,Column3 as buy_date,Column4 as quantity,Column5 as promotion
  FROM
( VALUES  ('0001',8,'2021-01-26',86654,'0001'),
           ('0002',2,'2021-02-26',78461,'0001'),
           ('0003',10,'2022-01-10',91436,'0002'),
 ('0004',4,'2021-03-27',1286,''),
  ('0005',1,'2021-02-28',48962,'0002'))
           )           
           
           select * from invoice_table;

3. โค้ดสร้าง Hardcoded Table บน pandas

สร้างเทเบิลเทสจาก Dictionary (column-wise)

import numpy as np
import pandas as pd
data_to_fill = {'header_1':['a','b','c'],'header_2':['d','e','f']}
df3 = pd.DataFrame(data=data_to_fill)
df3

สร้างเทเบิลเทสจาก Array (row-wise)

import numpy as np
import pandas as pd
df = pd.DataFrame(np.array([[1,2,3],[4,5,6],[7,8,9]]),
columns= ['header_1','header_2','header_3'],index=[7,8,9]) # ตรง parameter: index ไม่ต้องมีก็ได้ ถ้าไม่ใส่มันจะขึ้นเป็น 0 1 2 ให้อัตโนมัติ
df

กรณีไม่มี Parameter: index

import numpy as np
import pandas as pd
df2 = pd.DataFrame(np.array([[1,2,3],[4,5,6],[7,8,9]]),
columns= ['header_1','header_2','header_3'])  
df2

4. การ DESC เพื่อดูชื่อคอลัมน์ของเทเบิลบน BigQuery

จากเดิมที่ SQL ยี่ห้ออื่นเขียน DESC สั้นๆเพื่ออธิบาย Schema ของเทเบิลนั้น ๆ ได้
DESC `project_id.dataset_name.my_table_name`
แต่ใน BigQuery ต้องเขียนแบบคิวรี่ด้านล่างนี้แทน (ยาวเชียว)…



SELECT
    column_name,
    data_type,
    is_nullable
FROM `project_id.dataset_name`.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'my_table_name';