用于逐月显示过去数据的 SQL 脚本

Question

如果我的问题可能在其他地方得到回答，我深表歉意，我只是不确定搜索什么以了解它是否已在其他地方得到解答。 我是SQL的新手，并且一直在尝试查询一个数据库，该数据库根据数据库表中存在的百分比显示客户的系统正常运行时间，并将其报告为该月的平均值以及前几个月的过去性能。

例如，我取一个月的平均值并显示每个月，但随后根据当前月份(即 3 月、2 月、1 月等)对过去一年进行显示，总是恰好是过去一年的表现。

这是我目前的脚本，我根据客户 ID 平均工作一个月......现在，我想展示过去几年每个月的表现：

SQL查询：

select   cast (avg("DATA_POINT_DATA"."VALUE") as int) as "UP_VALUE",
"DATA_POINT_DATA"."UPLOAD_DATA_ID" as "CUSTOMER_ID"
from   "DB_TABLE"."DATA_POINT_DATA" "DATA_POINT_DATA",
"DB_TABLE"."CALC_DATA" "CALC_DATA" 
where   "DATA_POINT_DATA"."CALC_DATA_ID"="DATA_POINT_DATA"."ID"
and    "CALC_DATA"."NAME" ='CustomerUp' 
and   "DATA_POINT_DATA"."UPLOAD_DATA_ID" in ('123abc')
and  "UPLOAD_TIME" between ('01-FEB-17') and ('28-FEB-17') 
group by "DATA_POINT_DATA"."UPLOAD_DATA_ID";

查询输出：

UP_VALUE    CUSTOMER_ID
-------- --------------
100         123abc

预期输出：

MONTH    UP_VALUE    CUSTOMER_ID
----- ----------- --------------
FEB           100         123abc
JAN           100         123abc
DEC           100         123abc
NOV            90         123abc
OCT           100         123abc
SEP           100         123abc
AUG           100         123abc
JUL            89         123abc
JUN           100         123abc
MAY            75         123abc
APR           100         123abc
MAR           100         123abc
FEB            90         123abc

Answer 1

您有一个聚合avg()函数调用，并且您已经在选择列表中按非聚合列进行分组。要获取上一年每个月的数据(似乎截至上月底)，您只需将月份包含在选择列表和分组依据子句中：

select to_char(trunc("UPLOAD_TIME", 'MM'), 'MON') as "MONTH",
cast (avg("DATA_POINT_DATA"."VALUE") as int) as "UP_VALUE",
"DATA_POINT_DATA"."UPLOAD_DATA_ID" as "CUSTOMER_ID"
from "DB_TABLE"."DATA_POINT_DATA" "DATA_POINT_DATA",
"DB_TABLE"."CALC_DATA" "CALC_DATA" 
where "DATA_POINT_DATA"."CALC_DATA_ID"="DATA_POINT_DATA"."ID"
and "CALC_DATA"."NAME" ='CustomerUp' 
and "DATA_POINT_DATA"."UPLOAD_DATA_ID" in ('123abc')
and "UPLOAD_TIME" between ('01-MAR-16') and ('28-FEB-17') 
group by trunc("UPLOAD_TIME", 'MM'), "DATA_POINT_DATA"."UPLOAD_DATA_ID";

表达式trunc("UPLOAD_TIME", 'MM')为该列值提供该列值在每月第一天的午夜。这可以用于分组依据，因此一个月中的所有日期都被视为在同一天，即第一天。在选择列表中，可以将相同的表达式转换为仅显示月份名称的字符串。(我假设您的会话是英语，但如果不是，还有第三个参数需要to_char()来处理)。

该行

where "DATA_POINT_DATA"."CALC_DATA_ID"="DATA_POINT_DATA"."ID"

看起来很奇怪，目前两个表之间似乎没有任何真正的连接条件;我怀疑应该是：

where "DATA_POINT_DATA"."CALC_DATA_ID"="CALC_DATA"."ID"

但也许不是。

在执行以下操作时，您将依赖会话 NLS 设置：

and  "UPLOAD_TIME" between ('01-FEB-17') and ('28-FEB-17') 

您应该使用显式to_date()调用和格式掩码，最好不要假设会话语言是英语(这会影响月份名称处理)，但至少要做到：

and  "UPLOAD_TIME" between to_date('01-FEB-17', 'DD-MON-RR')
and to_date('28-FEB-17', 'DD-MON-RR') 

。但即使在这里，使用 4 位数的年份也是更合适的，如果您的任何列值在午夜之后有时间，您将丢失类似2017-02-28 00:00:01的数据。

您也不需要在此处的所有对象名称周围使用双引号;如果没有它们，对象名称的大小写就无关紧要，这可能会使其更容易阅读。您还应该使用 ANSI 联接语法;假设上面的where-sub观察是正确的，你可以做：

select to_char(trunc(upload_time, 'MM'), 'MON') as month,
cast (avg(data_point_data.value) as int) as up_value,
data_point_data.upload_data_id as customer_id
from db_table.data_point_data
join db_table.calc_data
on data_point_data.calc_data_id=calc_data.id
where calc_data.name ='CustomerUp' 
and data_point_data.upload_data_id = '123abc'
and upload_time >= add_months(trunc(sysdate, 'MM'), -12)
and upload_time < trunc(sysdate, 'MM')
group by trunc(upload_time, 'MM'), data_point_data.upload_data_id
order by trunc(upload_time, 'MM') desc;

对于日期范围，我基于当前日期，并使用>=和<而不是between，以避免非午夜时间的问题。您可以看到这些表达式的计算值：

select to_char(add_months(trunc(sysdate, 'MM'), -12), 'YYYY-MM-DD HH24:MI:SS') as from_time,
to_char(trunc(sysdate, 'MM'), 'YYYY-MM-DD HH24:MI:SS') as to_time
from dual;
FROM_TIME           TO_TIME            
------------------- -------------------
2016-03-01 00:00:00 2017-03-01 00:00:00

最后，cast(... as int)有点不寻常;根据您希望如何处理小数部分，您还可以查看trunc()，ceil()或floor()。

Answer 2

您只需要添加月份即可选择并按以下方式分组：

SELECT datepart(upload_time, DP_MONTH) as upload_month, Cast (Avg("data_point_data"."value") AS INT) AS "UP_VALUE", 
"data_point_data"."upload_data_id" AS "CUSTOMER_ID" 
FROM   "DB_TABLE"."data_point_data" "DATA_POINT_DATA", 
"DB_TABLE"."calc_data" "CALC_DATA" 
WHERE  "data_point_data"."calc_data_id" = "data_point_data"."id" 
AND "calc_data"."name" = 'CustomerUp' 
AND "data_point_data"."upload_data_id" IN ( '123abc' ) 
AND "upload_time" BETWEEN ( '01-FEB-17' ) AND ( '28-FEB-17' ) 
GROUP  BY datepart(upload_time, DP_MONTH), "data_point_data"."upload_data_id";