Oracle按天查询数据，并将填充数据为0

最新推荐文章于 2023-08-19 15:54:11 发布

ohoy

最新推荐文章于 2023-08-19 15:54:11 发布

阅读量1.2k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/weixin_40598838/article/details/109959835

版权

oracle 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

概述

在观看类别中有三类，手机、电脑、电视，有这样一个需求，要求按天统计出每个类别的观看数、订单数，如果当天没有数据，将三个类别的当天数据置为0，如果当天有数据，比如只有电脑这个类别的数据，则要求将手机、电视数据置0，补全。
在这里插入图片描述

sql

在对日期数据补全的操作上，通常有两种做法，一种是在java端补，另一种是用sql在数据库端补，一般要是能在数据库端补，进尽量在数据库端补，在java端补的话一个是麻烦，另一个是需要使用到各种循环，有点影响性能。

先来看sql怎么写吧，拿到需求后，第一个反应是，我需要做两个动作，一个是，如果当天没有数据，将三个类别的当天数据置为0，另一个是，如果当天有数据，比如只有电脑这个类别的数据，则要求将手机、电视数据置0，补全

对于第一个，在当天没有数据的情况下，如何将三个类别的当天数据置为0呐？首先我们是没办法一次补全三条的，但是我们得知道，这天没有数据，然后再java端用循环判断，如果VIEW_TYPE为空，那么add就行了，不是嘛？怎么做呐？

首先我们知道，再oracle中列出两个日期之间所有的天，用的是CONNECT BY ROWNUM，ROWNUM是干什么的呐？

在ORACLE中，当我们查到数据集后，可以为数据集加上一个ROWNUM伪列，它用来标识对符合条件结果的序列号。它总是从1开始排起

比如

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 6

在这里插入图片描述

看到了吧，它就是对结果集进行从1开始进行标识用的，那么我们如何用CONNECT BY ROWNUM进行日期计算呐？

看个东西

SELECT
ROWNUM,
TO_DATE('2020-11-10', 'yyyy-MM-dd') AS TIME_DATE,
TO_DATE('2020-11-10', 'yyyy-MM-dd')+ROWNUM  VIEW_TIME,
TO_DATE('2020-11-15', 'yyyy-MM-dd') - TO_DATE('2020-11-10', 'yyyy-MM-dd') AS TIME
FROM DUAL

在这里插入图片描述

看到了嘛？给定一个开始时间+ROWNUM就可以计算出其结束时间了

那么计算给定时间呐？

如下：

SELECT
ROWNUM,
TO_DATE('2020-11-10', 'yyyy-MM-dd') AS TIME_DATE,
TO_DATE('2020-11-10', 'yyyy-MM-dd')+ROWNUM  VIEW_TIME,
TO_DATE('2020-11-19', 'yyyy-MM-dd') - TO_DATE('2020-11-10', 'yyyy-MM-dd') AS TIME
FROM DUAL
CONNECT BY ROWNUM <= TO_DATE('2020-11-19', 'yyyy-MM-dd') - TO_DATE('2020-11-10', 'yyyy-MM-dd')

通过 CONNECT BY ROWNUM，就可以控制得到自己想要的时间了
在这里插入图片描述
如此完美，那么做到这，只要给一个右连接，就可以让我们得知道，这天没有数据了

来看

SELECT
a.VIEW_TYPE,b.VIEW_TIME,NVL(a.VIEW_NUMBER, 0),NVL(a.ORDER_QUANTITY, 0)
FROM
--按条件对数据进行筛选
(
SELECT
VIEW_TYPE,
TO_DATE(TO_CHAR(v.VIEW_TIME,'yyyy-MM-dd'),'yyyy-MM-dd') VIEW_TIME,
SUM(VIEW_NUMBER) VIEW_NUMBER,
SUM(ORDER_QUANTITY) ORDER_QUANTITY
FROM VIEW_DETAIL v
GROUP BY VIEW_TYPE,VIEW_TIME
ORDER BY VIEW_TIME

)a

RIGHT JOIN

(
SELECT TO_DATE('2020-11-10', 'yyyy-MM-dd')+ROWNUM  VIEW_TIME
FROM DUAL
CONNECT BY ROWNUM <= TO_DATE('2020-11-15', 'yyyy-MM-dd') - TO_DATE('2020-11-10', 'yyyy-MM-dd')

)b ON a.VIEW_TIME = b.VIEW_TIME
ORDER BY b.VIEW_TIME

在这里插入图片描述

看到了嘛，这样，我们就知道，某天是没有数据的了，hhh，太美好了

那么我们来解析一下上面这段sql吧

首先我们通过a表得到所有数据

SELECT
VIEW_TYPE,
TO_DATE(TO_CHAR(v.VIEW_TIME,'yyyy-MM-dd'),'yyyy-MM-dd') VIEW_TIME,
SUM(VIEW_NUMBER) VIEW_NUMBER,
SUM(ORDER_QUANTITY) ORDER_QUANTITY
FROM VIEW_DETAIL v
GROUP BY VIEW_TYPE,VIEW_TIME
ORDER BY VIEW_TIME