데이터베이스 질문과 답변

다중 case when / grouping 작업 관련 문의 0 10 3,509

by JOY [SQL Query] [2023.11.29 13:16:37]

.안녕하세요~!

오늘도 질문이 있어서 찾아왔습니다!

 

아래와 같이 A,B,C 값 별로 행을 가지고 오는 쿼리문을 작성하였는데

추가로 ABC의 합친 수량과, 근무시간을 더해서 수치를 구하는 쿼리를 추가하고 싶습니다.

검색하여서 grouping / rollup 을 사용하려 하니 이런 에러가 떠서 도움이 필요합니다ㅠ

line 15:8: The arguments to GROUPING() must be expressions referenced by the GROUP BY at the associated query level. Mismatch due to date .

현재 Zeppelin 사용하고 있으며 정확하게 mssql / mysql 인지를 모르겠습니다....... 

오라클 : NVL
MSSQL : ISNULL
MySQL : IFNULL
공통 : COALESCE

지난번 이 4개 알려주셨을때 공통 coalesce 빼고는 전부 작동이 되지 않았었습니다.

 

(select 문 만 가져옴)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select ct.code,
 week,
 case
  when date between 6 and 16 then 'A'
  when date  between 17 and 25 then 'B'
  when date between 26 and 35 then 'C'
 end as WORKTIME,
 case
  when grouping(date ) = 1 then 'total'
  end as total,
 sum(
  case
   when task = '작업'
   then 수량
  end) as 수량
 sum(cworkhours) as cworkhours,
 round(
  sum(
   case
    when task = '작업'
    then 수량
   end)
  / nullif(sum(수량) ,0)
 , 2) as 수치
by 마농 [2023.11.29 13:27:19]

전체 쿼리를 보여주세요.
MySQL 에 가장 가까운 듯 합니다만.
무슨 DB 인지 알아 두시는게 좋을 듯 합니다.

지난번에도 오류메시지를 보면 답이 보인다고 말씀 드렸었는데요.
오류 메시지 보면 그룹핑 하는데 date 가 이상하다는 내용이네요.
GROUPING 안에 오는 인자는 GROUP BY 기준 항목만 올 수 있습니다.

by JOY [2023.11.29 13:34:56]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
WITH Date AS (
SELECT iyyyy_1||mm AS mm
, iyyyy_1_wk AS week
, dates
from d.date)
  
select c.code,
 week,
 case
  when u.hour between 6 and 16 then 'a'
  when u.hour between 17 and 25 then 'b'
  when u.hour between 26 and 35 then 'c'
 end as hour,
 sum(
  case
   when  u.task = 'p'
    then u.qty
  end) as 수량,
 sum(u.workhour) as 시간,
 round(
  sum(
   case
    when u.task = 'p'
    then u.qty
   end)
  / nullif(sum(u.workhour) ,0)
 , 2) as 수치
 
from u.u
 
join (select center) c on c.id = u.id
join date on u.hour  = date.dates
 
where c.code = 'Joy1'
 and u.type= 'joy2'
 and u.hour between date('2023-07-01') and date(now())
 and u.deleted = 0
  
group by 1, 2, 3
order by 1, 2, 3

 

 

보안 문제로 문구를 좀 변경하였습니다. 해당 쿼리로  a,b,c 별 수량, 시간, 수치까지 구하는 쿼리는 정상 작동되었습니다!

by pajama [2023.11.29 14:47:19]

DB는 그동안 올려주신 글의 쿼리나 에러메시지를 볼때 presto 로 추측되긴 합니다.

https://prestodb.io/docs/current/index.html

by JOY [2023.11.29 14:52:31]

아하! 감사합니다. 

해당 쿼리에서 a,b,c 의 합계수량, 시간, 수치 구하는 함수는 어떤걸 사용해야 될까요?

by 마농 [2023.11.29 14:53:46]

1. 쿼리 컬럼명 변경하여 보여주실 때 예약어는 피해 주세요.
 - 예) date, hour 등등
2. 현재 정상 작동 쿼리의 결과표 대비 원하는 결과표 샘플을 보여주세요

by JOY [2023.11.29 15:11:20]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
WITH Date AS (
SELECT iyyyy_1||mm AS mm
, iyyyy_1_wk AS week
, dates
from d.date)
   
select c.code,
 week,
 case
  when u.시간 between 6 and 16 then 'a'
  when u.시간 between 17 and 25 then 'b'
  when u.시간 between 26 and 35 then 'c'
 end as 근무시간,
 sum(
  case
   when  u.task = 'p'
    then u.qty
  end) as 수량,
 sum(u.workhour) as 시간,
 round(
  sum(
   case
    when u.task = 'p'
    then u.qty
   end)
  / nullif(sum(u.workhour) ,0)
 , 2) as 수치
  
from u.u
  
join (select center) c on c.id = u.id
join date on u.hour  = date.dates
  
where c.code = 'Joy1'
 and u.type= 'joy2'
 and u.hour between date('2023-07-01') and date(now())
 and u.deleted = 0
   
group by 1, 2, 3
order by 1, 2, 3

 

 

code week 근무시간 수량 시간 수치
Joy1 47 a 500 40 12.5
Joy1 47 b 600 50 12
Joy1 47 c 700 60 11.67
Joy1 47 total 1800 150 12
Joy1 48 a 805 540 1.491
Joy1 48 b 540 124 4.355
Joy1 48 c 668 241 2.772
Joy1 48 total 2013 905 2.224
  49 a      
  49 b      
  49 c      
  49 total      

이런식의 a,b,c 의 값이 나왔고, 중간 토탈값이 필요합니다.

by 마농 [2023.11.29 15:31:31]

1. 예약어 컬럼명, 테이블명 사용 자제해 주세요.
2. 롤업 구문 동작하나요?
- 동작한다면? 사용 쿼리 및 결과 보여주세요.

by JOY [2023.11.29 15:59:28]

기능이 없다고 롤업 구문이 사용되지 않았습니다.

by 마농 [2023.11.29 16:06:55]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 간단한 쿼리로 롤업 가능성 테스트 해보세요.
-- GROUPING() 함수 사용도 가능한지 테스트 해보세요.
 
-- 1. 기본 유형
SELECT a, b, c
     , SUM(d) d
  FROM t
 GROUP BY ROLLUP(a, b, c)
;
-- 2. 부분 사용 유형
SELECT a, b, c
     , SUM(d) d
  FROM t
 GROUP BY a, b, ROLLUP(c)
;
-- 3. 괄호 사용 유형
SELECT a, b, c
     , SUM(d) d
  FROM t
 GROUP BY ROLLUP((a, b, c))
;
-- 4. MySQL 유형
SELECT a, b, c
     , SUM(d) d
  FROM t
 GROUP BY a, b, c
  WITH ROLLUP
;

 

by 마농 [2023.11.30 10:59:36]

올려주신 에러메시지를 보면 Grouping 이라는 함수가 있는 것으로 판단이 되고
Grouping 이라는 함수가 있다는 것은 롤업 기능도 있다는 것으로 추측할 수 있습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입