题目
有一张product表,里面有字段id(number)为产品id(唯一),product_date(date)为产出日期,product_count(int)为产量。现在,需要按周统计产量,周期为上周五到本周四。
- 请先按要求造数据,要求插入100万行数据,id为1到100万,product_date为20201231后但不超过昨天的随机日期,product_count为1到1000以内的随机整数。
- 根据自己造的数据,输出指定要求的统计结果,下图作为参考。
- 建表语句
CREATE TABLE product(
id NUMBER --产品id
,product_date DATE --产出日期
,product_count INT --产量
);
- 输入
- 输出
Oracle解答
造数据
初中级
BEGIN
FOR i IN 1 .. 1000000 LOOP
INSERT INTO product VALUES (
i
,to_date('20210101','yyyymmdd')+(trunc(sysdate)-to_date('20210101','yyyymmdd')-1)-(trunc(sysdate)-to_date('20210101','yyyymmdd')-1)*dbms_random.value
,1000-1000*dbms_random.value
);
END LOOP;
COMMIT;
END;
/
高级(使用批量绑定)
DECLARE
TYPE product_table_type IS TABLE OF product%rowtype
INDEX BY PLS_INTEGER;
product_table product_table_type;
p_index NUMBER;
BEGIN
FOR i IN 1 .. 1000000 LOOP
p_index := MOD(i,100000);
product_table(p_index).id := i;
product_table(p_index).product_date := to_date('20210101','yyyymmdd')+(trunc(sysdate)-to_date('20210101','yyyymmdd')-1)-(trunc(sysdate)-to_date('20210101','yyyymmdd')-1)*dbms_random.value;
product_table(p_index).product_count := 1000-1000*dbms_random.value;
IF p_index = 0 THEN
--这里批量绑定,为了防止内存不足,我们每100000做一次批量绑定
forall j IN product_table.FIRST .. product_table.LAST
INSERT INTO product VALUES product_table(j);
END IF;
END LOOP;
COMMIT;
END;
按周统计
WITH tmp_a AS (
SELECT
min(product_date) pdate_min --最小的日期
,max(product_date) pdate_max --最大的日期
FROM product
WHERE product_date IS NOT NULL
), tmp_b (start_date) AS (
--设置开始日期
--如果当天是周五周六周日则直接本周五
--如果当天非周五周六周日则取上周五
SELECT
CASE
WHEN trim(to_char(pdate_min,'day')) IN ('friday','saturday','sunday') THEN trunc(pdate_min,'iw')+4
ELSE trunc(pdate_min,'iw')-3
END
FROM tmp_a
UNION ALL
SELECT start_date+7 FROM tmp_b, tmp_a
WHERE to_char(start_date+7,'yyyymmdd') <= to_char(pdate_max,'yyyymmdd')
), tmp_c AS (
SELECT start_date,start_date+6 end_date FROM tmp_b
)
SELECT
to_char(b.start_date,'yyyymmdd')||'-'||to_char(b.end_date,'yyyymmdd') zb_span
,sum(product_count) product_count_z
FROM product a, tmp_c b
WHERE product_date >= start_date
AND product_date <= end_date
GROUP BY to_char(b.start_date,'yyyymmdd')||'-'||to_char(b.end_date,'yyyymmdd')
ORDER BY to_char(b.start_date,'yyyymmdd')||'-'||to_char(b.end_date,'yyyymmdd')
;
往期题目
上一题:【sql题 递归生成统计日期】
下一题:【sql题 巧用自连】