การเปรียบเทียบวันที่

การรายงานผลด้วย SQL

Tyler Pernes

Learning & Development Consultant

คำถามที่ต้องการตอบ

เดือนที่แล้ว vs เดือนก่อนหน้า?
ย้อนหลัง 7 วัน?

original_table
+------------+----------+
| date       | revenue  |
|------------+----------+
| 2018-01-01 | 400      |
| 2018-01-02 | 380      |
| 2018-01-03 | 625      |
+------------+----------+

การเปรียบเทียบแบบเดือนต่อเดือน

LAG(value, offset) คืนค่า value จากแถวที่ห่างออกไป offset แถว ก่อนหน้า แถวปัจจุบัน
LEAD(value, offset) คืนค่า value จากแถวที่ห่างออกไป offset แถว ถัดไป จากแถวปัจจุบัน

การเปรียบเทียบแบบเดือนต่อเดือน

ขั้นตอนที่ 1: แสดงรายรับรายเดือน

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev
FROM original_table
GROUP BY month;

ขั้นตอนที่ 2: รายรับของเดือนก่อนหน้า

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev,
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date)) AS prev_rev
FROM original_table
GROUP BY month;

การเปรียบเทียบแบบเดือนต่อเดือน

ขั้นตอนที่ 3: คำนวณการเปลี่ยนแปลงเป็นเปอร์เซ็นต์

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev,
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date)) AS prev_rev,
    SUM(revenue)
    /
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date))-1 AS perc_change
FROM original_table
GROUP BY month;

การเปรียบเทียบแบบเดือนต่อเดือน

ขั้นตอนที่ 3: คำนวณการเปลี่ยนแปลงเป็นเปอร์เซ็นต์

+--------+--------------+-----------+--------------+
| month  | current_rev  | prev_rev  | perc_change  |
|--------+--------------+-----------+--------------|
| 01     | 15000        | null      | null         |
| 02     | 14000        | 15000     | -.06         |
| 03     | 21000        | 14000     | .50          |
+--------+--------------+-----------+--------------+

การคำนวณแบบ Rolling

นำข้อมูลเฉพาะ 7 แถว เท่านั้น

คำสั่งใหม่: ROWS BETWEEN

SUM(value) OVER (ORDER BY value ROWS BETWEEN N PRECEDING AND CURRENT ROW)

การคำนวณแบบ Rolling

คิวรีสำหรับ Rolling Sum

SELECT 
    date,
    SUM(SUM(revenue)) OVER 
    (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_revenue
FROM original_table
GROUP BY date;

ตารางใหม่: web_data

web_data
+------------+-------------+--------+
| date       | country_id  | view   |
|------------+-------------+--------|
| 2018-01-01 | 1           | 24313  |
| 2018-01-01 | 2           | 3768   |
| 2018-01-01 | 3           | 26817  |
+------------+-------------+--------+

มาฝึกกันเถอะ!

การรายงานผลด้วย SQL