Asked Jan 14th, 2022 1:47 a.m. 207 1 5
  • 207 1 5
+1

Cho em hỏi về query ?

Share
  • 207 1 5

Em có 1 bảng chứa : name, status(1,2), created_at. danh sách các user và trạng thái.

Yêu cầu: Query để hiển thị dữ liệu theo từng tháng và tỉ lệ status = 2 / status = 1 của từng tháng.

Ví dụ bảng có 100 item cho 10 tháng. Em phải hiển thị hiển thị dữ liệu ra bên ngoài theo các tháng. mỗi tháng là 1 hàng với % status = 2 / status = 1 của tháng đấy ( như kiểu % status của tháng trên 60% thì tháng đấy có trạng thái là warning, dưới 60 là báo động, trên 70 là tốt).

Em xin cảm ơn.

5 ANSWERS


Answered Jan 18th, 2022 9:45 a.m.
Accepted
0

Có lẽ đây là kết quả bạn mong muốn:

SELECT month, IFNULL(ROUND((active / deactive) * 100, 1), 100) AS 'ratio(active/deactive)'
FROM (SELECT month,
             SUM(IF(status = 1, count, 0)) as active,
             SUM(IF(status = 0, count, 0)) as deactive
      FROM (SELECT MONTH(created_date) month, status, COUNT(*) count
            FROM users
            GROUP BY MONTH(created_date), status) u
      GROUP BY u.month) u1;

Kết quả:

+-------+------------------------+
| month | ratio(active/deactive) |
+-------+------------------------+
|     1 |                   50.0 |
|     2 |                  100.0 |
|     7 |                  100.0 |
|     5 |                  100.0 |
|     6 |                    0.0 |
+-------+------------------------+

Share
Answered Jan 14th, 2022 2:46 a.m.
+1

Vì không có bảng giống như bạn mô tả nên mình dùng bảng tương tự nhé.

Bạn tham khảo rồi thay đổi tên cho hợp lý

SELECT SUBSTR(bm.touroku_date,5,2) AS month, COUNT(bm.del_flg) AS sum,
  SUM(CASE WHEN del_flg = 1 THEN 1 ELSE 0 END) AS deleted_sum,
  SUM(CASE WHEN del_flg = 0 THEN 1 ELSE 0 END) AS not_yet_delete_sum,
  CONCAT(
    ROUND(SUM(CASE WHEN del_flg = 1 THEN 1 ELSE 0 END)*1.0/count(bm.del_flg)*100,2),
    '/',
    ROUND(SUM(CASE WHEN del_flg = 0 THEN 1 ELSE 0 END)*1.0/count(bm.del_flg)*100,2)
  ) AS percent
FROM bookmark bm
WHERE bm.touroku_date >'2018010100000' AND bm.touroku_date <'2018123100000'
GROUP BY month
ORDER BY month;

Kết quả

 month | sum  | deleted_sum | not_yet_delete_sum |   percent
-------+------+-------------+--------------------+-------------
 01    |  727 |         138 |                589 | 18.98/81.02
 02    |  418 |          81 |                337 | 19.38/80.62
 03    | 2979 |         798 |               2181 | 26.79/73.21
 04    | 1941 |         420 |               1521 | 21.64/78.36
 05    | 1585 |         276 |               1309 | 17.41/82.59
 06    |  548 |          82 |                466 | 14.96/85.04
 08    |    1 |           0 |                  1 | 0.00/100.00
 10    |    1 |           0 |                  1 | 0.00/100.00
 11    |    2 |           2 |                  0 | 100.00/0.00

Share
Avatar CongHD @conghdql4
Jan 14th, 2022 3:10 a.m.

P/s: Có vẻ như sai yêu cầu bạn cần nhưng dựa theo SQL đó có thể modify lại cho đúng ý bạn nhé!

0
| Reply
Share
Answered Jan 14th, 2022 3:20 a.m.
+1

Có một cách đơn giản là bạn query ra số lượng theo từng status, của từng tháng. Rồi khi hiển thị ra view thì bạn mới tính % sau 😂

Ví dụ bạn có thể dùng câu query dưới đây

SELECT date_trunc('month', "created_at") as "created_at_month", "status", count(*) AS "count"
FROM "tablename"
GROUP BY "created_at_month", "status"
ORDER BY "created_at_month" ASC

khi đó thì kết quả sẽ được nhóm theo các cặp monthstatus kiểu như sau

created_at_month    status    count
2022-01-01          1         5
2022-01-01          2         3
2022-01-01          3         2
2022-02-01          1         1
2022-02-01          2         0
2022-02-01          3         4

lúc đó phần tính toán hiển thị ra % ở view chắc cũng không khó khăn gì 🤔

Share
Avatar CongHD @conghdql4
Jan 14th, 2022 3:33 a.m.

Tùy mục đích sử dụng bạn ạ. Không biết khách hàng bên VN mình như thế nào chứ bên Nhật có kiểu khách hàng họ muốn lấy dữ liệu mà mình trích xuất trực tiếp từ SQL sang excel luôn chứ không làm chức năng trên màn hình đâu.

+1
| Reply
Share
Avatar Tran Duc Thang @thangtd90
Jan 14th, 2022 4:15 a.m.

@conghdql4 uhm đúng như bạn nói thì để export thẳng ra file .csv thì câu lệnh ở trên vẫn chưa giải quyết được yêu cầu của bài toán (^^;)

Tại mình thấy phần câu hỏi có mấy tag là Laravel với Eloquent thì mình đang hiểu là dùng Laravel để lấy ra dữ liệu, xong chắc hiển thị ra phần view, thì mình nghĩ cách ở trên cũng đơn giản, dễ viết câu lệnh và output trả ra cũng dễ dàng xử lý tiếp bằng PHP 😂

0
| Reply
Share
Avatar CongHD @conghdql4
Jan 14th, 2022 5:50 a.m.

@thangtd90 à nhỉ, mình không để ý phần tag 😄

0
| Reply
Share
Answered Jan 14th, 2022 9:12 a.m.
+1
User::select(DATE_FORMAT(created_at, '%m') as month "), DB::raw("SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END)  as status1, SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as status2  )
        ->groupBy('month')
        ->get();
Share
Avatar Vũ A @vukt921538
Jan 18th, 2022 9:17 a.m.
Jun 14th, 2022 8:17 a.m.
Answered Jun 14th, 2022 8:18 a.m.
0

Bạn có thể sử dụng Group by + MONTH(<date>) để gom nhóm theo tháng, dùng SUM + CASE WHEN để tính cột % status nhé, cột tính toán mình để là status = 2 / status = 1 nhé, nếu muốn quy ra % thì thêm phép toán vào nè. (Mình đang dùng MySQL)

SELECT
   MONTH(created_at) AS month,
   (
      SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) / SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END)
   ) AS '% status'
FROM `users`
GROUP BY MONTH(created_at)
Share
Viblo
Let's register a Viblo Account to get more interesting posts.