mysql count 함수와 case 를 사용해서 여러 조건의 값을 출력

29.08.2022 — MYSQL, 2022 — 6 min read

2022-08-29

배경

프론트 화면에서 기부 목록을 가져온다. 이때 전체 기부 횟수 올해 기부 횟수 올해 기부영수증 횟수 신청이 가능한데 아직 신청 하지 않은 횟수(올해) 를 한번에 보고 싶다는 요청이 있었다.

기존에는 사용자의 기부 목록 조회 api 를 queryParams 만 다르게 사용하셔서 4번 호출하셨다고한다..

어떻게 한번만 불러서 4개의 횟수 데이터를 가져올 수 있을까?

사용자의 기부 목록 조회 api 는 여러 테이블이 left,inner join 된 쿼리가 존재한다 if 문에서 queryParams 조건만 다르면 된다

최초시도..

우선 service 에서 4개의 조건을 map으로 만들어서 4번 db 를 조회하는 코드를 만들었고 실행시간이 4초가 걸렸다.

이렇게 여러번 가는 것은 느리며 좋지 않은 코드라고 생각하고 한번에 여러경우를 알 수 있는 case 문 과 count 함수를 사용하기로 했다

SELECT
COUNT(*) as allDonationsCount,
COUNT(CASE WHEN e.receipt_available+s.receipt_available = 2 and r.donation_id is null and( mid(JSON_EXTRACT(donations.created, '$.datetime'), 2, 10) >= '2022-01-01' and  (mid(JSON_EXTRACT(donations.created, '$.datetime'), 2, 10) <= '2022-12-31' ))then 1 else null END) as notAppliedReceiptsCount,
COUNT(CASE WHEN ( mid(JSON_EXTRACT(donations.created, '$.datetime'), 2, 10) >= '2022-01-01' and  (mid(JSON_EXTRACT(donations.created, '$.datetime'), 2, 10) <= '2022-12-31')) then 1 else null END)  as thisYearDonationsCount,
COUNT(CASE WHEN (r.donation_id > 0  and mid(JSON_EXTRACT(donations.created, '$.datetime'), 2, 10) >= '2022-01-01' and  (mid(JSON_EXTRACT(donations.created, '$.datetime'), 2, 10) <= '2022-12-31')) then 1 else null END)  as appliedReceiptsCount
FROM donations
INNER JOIN eval_types as e ON donations.eval_type = e.type and e.deleted_at is null
INNER JOIN statuses as s ON donations.status = s.status
LEFT JOIN donors as r ON donations.id = r.donation_id and r.deleted_at is null WHERE (donations.deleted_at is null)
AND (donations.member_id = 1) ORDER BY donations.id DESC;

2023-09-14

배경

예약발송 기능이 기존에 있었는데 즉시발송 기능이 추가되었다 각 매장마다 월 5회로 예약건수를 제한했었다 기능 추가로 즉시발송 건수와 예약건수 합계가 5회로 제한되도록 결정했고

하나의 테이블에서 특정 매장에 월 발송건,예약건을 조회하는 방법을 찾아야햇다

발송타입이 A인 경우와 예약일이 2023년 7월인 경우를 카운트하고, 발송타입이 B인 경우와 발송일이 2023년 7월인 경우를 카운트하여 발송건수와 예약건수_7월을 계산

SELECT
    SUM(CASE WHEN scheduled_type = 'A'  THEN 1 ELSE 0 END) AS 즉시발송건, --(4) scheduled_type 이 즉시발송건인 경우 횟수 합계
    SUM(CASE WHEN scheduled_type = 'B'  THEN 1 ELSE 0 END) AS 예약건  --(4) scheduled_type 이 예약건인 경우 횟수 합계
FROM 테이블이름
WHERE
code ='123'  -- (1)특정 매장에
AND ((scheduled_type = 'B' AND scheduled_date LIKE '202309%') OR  -- (2)2023년 9월 예약내역
      (scheduled_type = 'A' AND sent_date LIKE '2023-09%')); -- 2023년 (3)9월 즉시발송내역 중에서

2023-09-22

마이그레이션을 위해서 기존 테이블에 조건에 따라서 상태 컬럼을 추가해야 했다 임의로 select 문으로 가상의 컬럼(status 컬럼)을 생성해서 값을 조회햇다. 상태: 발송없음,발송대기,발송불가,발송실패,발송완료 5가지

조건별 CASE 문

(CASE
    WHEN scheduled_type = 'none' THEN 'NoDispatch'
    WHEN (scheduled_type IN ('scheduled', 'immediately') AND sent = 0) THEN
        CASE
            WHEN scheduled_date <> '' AND STR_TO_DATE(scheduled_date, '%Y%m%d') >= CURDATE() and closed = 0 THEN 'PendingDispatch'
            WHEN scheduled_date <> '' AND STR_TO_DATE(scheduled_date, '%Y%m%d') < CURDATE() THEN 'DispatchFailed'
            ELSE 'UnableToDispatch'
        END
    WHEN sent = 1 THEN 'Dispatched'
END)

* scheduled_date <> '' 조건을 사용하여 scheduled_date가 비어있지 않은 경우만 조회되도록

해당 조건으로 status 포함된 값 조회

select *, (조건별 CASE 문) as status from 테이블;

id	status	...
1	NoDispatch	...
2	Dispatched	...
3	Dispatched	...
4	PendingDispatch	...
5	Dispatched	...
6	PendingDispatch	...
7	Dispatched	...

테이블에 업데이트를

UPDATE store_news SET sent_status = ( 조건별 CASE 문)

* tip

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.	0.0064 sec

-> 해당 에러 발생시 마지막줄에 where id > 0 과 같은 조건을 추가해야 한다

각 조건별 갯수가 궁금할때 (상태별 : 횟수)

SELECT status, COUNT(*) as record_count
FROM (
SELECT *,
(조건별 CASE 문) as status
FROM 테이블
) AS subquery
GROUP BY status;

status	record_count
NoDispatch	1
PendingDispatch	2
Dispatched	4

20231206

SELECT
	d.name as 매장,
	COUNT(*) as 접수건,
	SUM(CASE WHEN d.status in('A','B','C','D')  THEN 1 ELSE 0 END) AS 기부건
FROM donations as d
inner join
	standbys as s on  d.id = s.donation_id
where d.code in (1,2,3,4,5,6,7)
	and (mid(JSON_EXTRACT(d.created, '$.datetime'), 2, 10) >= '2023-11-01')
	and (mid(JSON_EXTRACT(d.created, '$.datetime'), 2, 10) <= '2023-11-30')
group by d.code

매장	접수건	기부건
1매장	100	80
2매장	230	200
3매장	130	110

20240502

How to use COUNT with multiple columns

quantity 수량수 와 a_quantity 수량수 를 한눈에 볼 수 있다

SELECT count(*),count(quantity),count(a_quantity) FROM A where  status = 'AAA' ;

두 값이 일치하는지 결과를 반환하는 쿼리는?

SELECT
    CASE
        WHEN count(quantity) = count(a_quantity) THEN 'O'
        ELSE 'X'
    END AS 결과
FROM A
WHERE status = 'AAA';