Yêu cầu thg 1 14, 2022 1:47 SA 194 1 5
  • 194 1 5
+1

Cho em hỏi về query ?

Chia sẻ
  • 194 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 CÂU TRẢ LỜI


Đã trả lời thg 1 18, 2022 9:45 SA
Đã được chấp nhận
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 |
+-------+------------------------+

Chia sẻ
Đã trả lời thg 1 14, 2022 2:46 SA
+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

Chia sẻ
Avatar CongHD @conghdql4
thg 1 14, 2022 3:10 SA

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é!

Đã trả lời thg 1 14, 2022 3:20 SA
+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ì 🤔

Chia sẻ
Avatar CongHD @conghdql4
thg 1 14, 2022 3:33 SA

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.

Avatar Tran Duc Thang @thangtd90
thg 1 14, 2022 4:15 SA

@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 😂

Avatar CongHD @conghdql4
thg 1 14, 2022 5:50 SA

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

Đã trả lời thg 1 14, 2022 9:12 SA
+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();
Chia sẻ
Avatar Vũ A @vukt921538
thg 1 18, 2022 9:17 SA
thg 6 14, 2022 8:17 SA
Đã trả lời thg 6 14, 2022 8:18 SA
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)
Chia sẻ
Viblo
Hãy đăng ký một tài khoản Viblo để nhận được nhiều bài viết thú vị hơn.
Đăng kí