Cho em hỏi về query ?
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 CÂU TRẢ LỜI
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 |
+-------+------------------------+
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
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é!
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 month
và status
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ì
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.
@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
@thangtd90 à nhỉ, mình không để ý phần tag
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();
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)