날짜 간격으로 여러 열 그룹 검색

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 <> 여기에 바이올린