按日期间隔检索多列组 [英] retrieve multible columns group by date intervall
本文介绍了按日期间隔检索多列组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想从一个表中检索多个列，整个月的重量数据总和.我需要帮助的是我想将结果分为当月 1-15 和第二行 16-31 的两部分总和.
选择 TO_CHAR(sysdate) 虚拟(SELECT(SUM(B.SCALE_WEIGHT) FROM TRACKING.DATALOG_TAB B WHERE B.MATERIALID= 1AND B.SCALE_EVENTDATE BETWEEN TO_DATE(TRUNC(TO_DATE('2020-10-1', 'YYYY-MM-DD'),'MONTH')) 和 TO_DATE(TRUNC(TO_DATE('2020-11-1', 'YYYY-MM-DD'),'MONTH')+16)) 作为 MTRL1，(SELECT(SUM(B.SCALE_WEIGHT) FROM TRACKING.DATALOG_TAB B WHERE B.MATERIALID= 2AND B.SCALE_EVENTDATE BETWEEN TO_DATE(TRUNC(TO_DATE('2020-10-1', 'YYYY-MM-DD'),'MONTH')) 和 TO_DATE(TRUNC(TO_DATE('2020-11-1', 'YYYY-MM-DD'),'MONTH')+16)) 作为 MTRL2从双GROUP BY(类似这样的东西 - 1-15 和 16-31)；更新
结果应该是这样的
解决方案
如果你有示例数据:
CREATE TABLE tracking.datalog_tab ( materialid, scale_eventdate, scale_weight ) AS选择 1, 日期 '2020-10-01', 1 from DUAL UNION ALL选择 1, 日期 '2020-10-15', 2 from DUAL UNION ALL选择 1, 日期 '2020-10-16', 3 from DUAL UNION ALL选择 1, 日期 '2020-10-31', 4 from DUAL UNION ALL选择 2, 日期 '2020-10-01', -1 from DUAL UNION ALL选择 2, 日期 '2020-10-15', -2 from DUAL UNION ALL选择 2, 日期 '2020-10-16', -3 from DUAL UNION ALL选择 2, 日期 '2020-10-31', -4 from DUAL;您可以使用:
SELECT MATERIALID,案件何时提取(从 SCALE_EVENTDATE 开始)<= 15然后'1-15'其他 '16-31'END AS day_range,总和(SCALE_WEIGHT)来自 TRACKING.DATALOG_TAB材料在 ( 1, 2 ) 中的位置AND SCALE_EVENTDATE >= DATE '2020-10-01'AND SCALE_EVENTDATE <日期 '2020-11-01'通过...分组材料编号，案件何时提取(从 SCALE_EVENTDATE 开始)<= 15然后'1-15'其他 '16-31'结尾;输出:
<块引用><前>材料编号 |DAY_RANGE |总和(SCALE_WEIGHT)---------: |:-------- |----------------:1 |1-15 |32 |1-15 |-31 |16-31 |72 |16-31 |-7或者，如果您希望它们作为列，则 PIVOT:
SELECT *从 (选择材料 ID，案件何时提取(从 SCALE_EVENTDATE 开始)<= 15然后'1-15'其他 '16-31'END AS day_range,SCALE_WEIGHT来自 TRACKING.DATALOG_TAB材料在 ( 1, 2 ) 中的位置AND SCALE_EVENTDATE >= DATE '2020-10-01'AND SCALE_EVENTDATE <日期 '2020-11-01')枢轴 (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));输出:
<块引用><前>地铁1_01_15 |地铁1_16_31 |地铁2_01_15 |地铁2_16_31----------: |----------: |----------: |----------:3 |7 |-3 |-7db<>fiddle 这里
更新
SELECT *从 (选择材料 ID，案件何时提取(从 SCALE_EVENTDATE 开始)<= 15然后'1-15'其他 '16-31'结尾||TO_CHAR( scale_eventdate, 'Mon' ) AS date_range,SCALE_WEIGHT从/*TRACKING.*/DATALOG_TAB其中材料在 ( 1, 2, 3 )AND SCALE_EVENTDATE >= DATE '2020-10-01'AND SCALE_EVENTDATE <日期 '2020-11-01')枢轴 (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选择 1, 日期 '2020-10-01', 25 from DUAL UNION ALL选择 1, 日期 '2020-10-15', 75 from DUAL UNION ALL选择 1, 日期 '2020-10-16', 125 from DUAL UNION ALL选择 1, 日期 '2020-10-31', 375 from DUAL UNION ALL选择 2, 日期 '2020-10-01', 90 from DUAL UNION ALL选择 2, 日期 '2020-10-15', 110 from DUAL UNION ALL选择 2, 日期 '2020-10-16', 90 from DUAL UNION ALL选择 2, 日期 '2020-10-31', 125 from DUAL UNION ALL选择 3, 日期 '2020-10-01', 120 from DUAL UNION ALL选择 3, 日期 '2020-10-16', 120 from DUAL UNION ALL选择 3, 日期 '2020-10-31', 240 from DUAL;输出:
<块引用><前>DATE_RANGE |SUM_MTRL1_WEIGHT |SUM_MTRL2_WEIGHT |SUM_MTRL3_WEIGHT:--------- |---------------: |---------------: |---------------:10 月 1-15 日 |100 |200 |12010 月 16-31 日 |500 |第215话360db<>fiddle 这里
I want to retrieve multible columns, sum of weight data from a table over a whole month. what I need help with is that I want to group the result into 2 parts sum of 1-15 of the month and second line 16-31 of the month.
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);
UPDATE
the result should look like this
解决方案
If you have the sample data:
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;
You can use:
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;
Which outputs:
MATERIALID | DAY_RANGE | SUM(SCALE_WEIGHT) ---------: | :-------- | ----------------: 1 | 1-15 | 3 2 | 1-15 | -3 1 | 16-31 | 7 2 | 16-31 | -7
Or, if you want them as columns then 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
)
);
Which outputs:
MTRL1_01_15 | MTRL1_16_31 | MTRL2_01_15 | MTRL2_16_31 ----------: | ----------: | ----------: | ----------: 3 | 7 | -3 | -7
db<>fiddle here
Update
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
)
);
Which, for the sample data:
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;
Outputs:
DATE_RANGE | SUM_MTRL1_WEIGHT | SUM_MTRL2_WEIGHT | SUM_MTRL3_WEIGHT :--------- | ---------------: | ---------------: | ---------------: 1-15 Oct | 100 | 200 | 120 16-31 Oct | 500 | 215 | 360
db<>fiddle here
这篇关于按日期间隔检索多列组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
