날짜 간격으로 여러 열 그룹 검색
Nov 13 2020
한 달 동안 테이블에서 가중치 데이터의 합계 인 다중 열을 검색하고 싶습니다. 내가 도움이 필요한 것은 그 달의 1-15와 그 달의 두 번째 줄 16-31의 합계 2 부분으로 결과를 그룹화하고 싶다는 것입니다.
Select TO_CHAR(sysdate) dummy
(SELECT(SUM(B.SCALE_WEIGHT) FROM TRACKING.DATALOG_TAB B WHERE B.MATERIALID= 1
AND B.SCALE_EVENTDATE BETWEEN TO_DATE(TRUNC(TO_DATE('2020-10-1', 'YYYY-MM-
DD'),'MONTH')) AND TO_DATE(TRUNC(TO_DATE('2020-11-1', 'YYYY-MM-DD'),
'MONTH')+16)) as MTRL1,
(SELECT(SUM(B.SCALE_WEIGHT) FROM TRACKING.DATALOG_TAB B WHERE B.MATERIALID= 2
AND B.SCALE_EVENTDATE BETWEEN TO_DATE(TRUNC(TO_DATE('2020-10-1', 'YYYY-MM-
DD'),'MONTH')) AND TO_DATE(TRUNC(TO_DATE('2020-11-1', 'YYYY-MM-DD'),
'MONTH')+16)) as MTRL2
FROM DUAL
GROUP BY(somthing like this - 1-15 and 16-31);
최신 정보
결과는 다음과 같아야합니다

답변
Littlefoot Nov 13 2020 at 12:15
나에게는 다음과 같이 보입니다.
select
sum(case when b.materialid = 1 and
to_number(to_char(b.scale_eventdate, 'dd')) between 1 and 15 then
b.scale_weight
end) mtrl1,
--
sum(case when b.materialid = 2 and
to_number(to_char(b.scale_eventdate, 'dd')) between 16 and 31 then
b.scale_weight
end) mtrl2
from datalog_tab b
where to_char(b.scale_eventdate, 'yyyymm') = '202010'
즉, scale_eventdate
열의 날짜 가 해당 월의 상반기 또는 하반기에 속 하는지 확인 하고 scale_weight
그에 따라 합계 합니다.
MT0 Nov 13 2020 at 12:22
샘플 데이터가있는 경우 :
CREATE TABLE tracking.datalog_tab ( materialid, scale_eventdate, scale_weight ) AS
SELECT 1, DATE '2020-10-01', 1 FROM DUAL UNION ALL
SELECT 1, DATE '2020-10-15', 2 FROM DUAL UNION ALL
SELECT 1, DATE '2020-10-16', 3 FROM DUAL UNION ALL
SELECT 1, DATE '2020-10-31', 4 FROM DUAL UNION ALL
SELECT 2, DATE '2020-10-01', -1 FROM DUAL UNION ALL
SELECT 2, DATE '2020-10-15', -2 FROM DUAL UNION ALL
SELECT 2, DATE '2020-10-16', -3 FROM DUAL UNION ALL
SELECT 2, DATE '2020-10-31', -4 FROM DUAL;
당신이 사용할 수있는:
SELECT MATERIALID,
CASE
WHEN EXTRACT( DAY FROM SCALE_EVENTDATE ) <= 15
THEN ' 1-15'
ELSE '16-31'
END AS day_range,
SUM(SCALE_WEIGHT)
FROM TRACKING.DATALOG_TAB
WHERE MATERIALID IN ( 1, 2 )
AND SCALE_EVENTDATE >= DATE '2020-10-01'
AND SCALE_EVENTDATE < DATE '2020-11-01'
GROUP BY
MATERIALID,
CASE
WHEN EXTRACT( DAY FROM SCALE_EVENTDATE ) <= 15
THEN ' 1-15'
ELSE '16-31'
END;
출력되는 내용 :
MATERIALID | DAY_RANGE | SUM (SCALE_WEIGHT) --------- : | : -------- | ---------------- : 1 | 1-15 | 삼 2 | 1-15 | -삼 1 | 16-31 | 7 2 | 16-31 | -7
또는 열로 원하는 경우 다음을 수행하십시오 PIVOT
.
SELECT *
FROM (
SELECT MATERIALID,
CASE
WHEN EXTRACT( DAY FROM SCALE_EVENTDATE ) <= 15
THEN ' 1-15'
ELSE '16-31'
END AS day_range,
SCALE_WEIGHT
FROM TRACKING.DATALOG_TAB
WHERE MATERIALID IN ( 1, 2 )
AND SCALE_EVENTDATE >= DATE '2020-10-01'
AND SCALE_EVENTDATE < DATE '2020-11-01'
)
PIVOT (
SUM( scale_weight ) FOR ( materialid, day_range ) IN (
( 1, ' 1-15' ) AS mtrl1_01_15,
( 1, '16-31' ) AS mtrl1_16_31,
( 2, ' 1-15' ) AS mtrl2_01_15,
( 2, '16-31' ) AS mtrl2_16_31
)
);
출력되는 내용 :
MTRL1_01_15 | MTRL1_16_31 | MTRL2_01_15 | MTRL2_16_31 ---------- : | ---------- : | ---------- : | ---------- : 3 | 7 | -3 | -7
db <> 여기에 바이올린
최신 정보
SELECT *
FROM (
SELECT MATERIALID,
CASE
WHEN EXTRACT( DAY FROM SCALE_EVENTDATE ) <= 15
THEN ' 1-15 '
ELSE '16-31 '
END
|| TO_CHAR( scale_eventdate, 'Mon' ) AS date_range,
SCALE_WEIGHT
FROM /*TRACKING.*/DATALOG_TAB
WHERE MATERIALID IN ( 1, 2, 3 )
AND SCALE_EVENTDATE >= DATE '2020-10-01'
AND SCALE_EVENTDATE < DATE '2020-11-01'
)
PIVOT (
SUM( scale_weight ) FOR materialid IN (
1 AS sum_mtrl1_weight,
2 AS sum_mtrl2_weight,
3 AS sum_mtrl3_weight
)
);
샘플 데이터의 경우 :
CREATE TABLE /*TRACKING.*/datalog_tab ( materialid, scale_eventdate, scale_weight ) AS
SELECT 1, DATE '2020-10-01', 25 FROM DUAL UNION ALL
SELECT 1, DATE '2020-10-15', 75 FROM DUAL UNION ALL
SELECT 1, DATE '2020-10-16', 125 FROM DUAL UNION ALL
SELECT 1, DATE '2020-10-31', 375 FROM DUAL UNION ALL
SELECT 2, DATE '2020-10-01', 90 FROM DUAL UNION ALL
SELECT 2, DATE '2020-10-15', 110 FROM DUAL UNION ALL
SELECT 2, DATE '2020-10-16', 90 FROM DUAL UNION ALL
SELECT 2, DATE '2020-10-31', 125 FROM DUAL UNION ALL
SELECT 3, DATE '2020-10-01', 120 FROM DUAL UNION ALL
SELECT 3, DATE '2020-10-16', 120 FROM DUAL UNION ALL
SELECT 3, DATE '2020-10-31', 240 FROM DUAL;
출력 :
DATE_RANGE | SUM_MTRL1_WEIGHT | SUM_MTRL2_WEIGHT | SUM_MTRL3_WEIGHT : --------- | --------------- : | --------------- : | --------------- : 10 월 1 일 -15 일 | 100 | 200 | 120 10 월 16-31 일 | 500 | 215 | 360
db <> 여기에 바이올린