So sánh theo ngày

Lập báo cáo trong SQL

Tyler Pernes

Learning & Development Consultant

Câu hỏi cần trả lời

Tháng trước so với tháng liền trước?
Cuộn 7 ngày?

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

So sánh tháng này với tháng trước

LAG(value, offset) trả về value từ số hàng bằng offset trước hàng hiện tại.
LEAD(value, offset) trả về value từ số hàng bằng offset sau hàng hiện tại.

So sánh tháng này với tháng trước

Bước 1: hiển thị doanh thu theo tháng

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

Bước 2: doanh thu tháng trước

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;

So sánh tháng này với tháng trước

Bước 3: tính phần trăm thay đổi

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;

So sánh tháng này với tháng trước

Bước 3: tính phần trăm thay đổi

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

Tính toán cuộn

Chỉ tính 7 hàng

Mệnh đề mới: ROWS BETWEEN

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

Tính toán cuộn

Truy vấn tổng cuộn

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;

Bảng mới: web_data

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

Let's practice!

Lập báo cáo trong SQL