时间戳

首页 > 解决方案 > 每辆车相对有 Count(For that Day)、最近 10 天的计数和最近 20 天的计数

sql - 每辆车相对有 Count(For that Day)、最近 10 天的计数和最近 20 天的计数

问题描述

我试图查看当天车辆的销售情况并创建另外两个列，告诉我过去 10 天的销售计数和过去 20 天的销售计数。同一天和同一辆车可能有多个销售。我的目标是获取不同的车辆和日期并查看他们的销售量。

N 天数应该与该行的日期相关。

我的数据看起来像：

 Vehicle_ID       Sales_Date
 X500             01/03/2020 02:00:00 PM
 X500             01/11/2020 05:00:00 PM
 X500             01/25/2020 06:00:00 PM
 X500             01/25/2020 01:00:00 PM
 X500             02/13/2020 06:00:00 PM
 X500             02/21/2020 02:00:00 PM

我的目标表应该看起来像 

 Vehicle_ID       Sales_Date     Sales_Count    Sales_Count_last_10     Sales_Count_last_20
 X500             01/03/2020         1                 1                      1
 X500             01/11/2020         1                 2                      2
 X500             01/25/2020         2                 2                      3
 X500             02/13/2020         1                 1                      4
 X500             02/21/2020         1                 2                      2

我尝试了以下查询：

SELECT TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') Sales_Date1, Vehicle_ID, 
       COUNT(*) OVER (PARTITION BY Vehicle_ID, TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')) SALES_COUNTS,
       SUM(CASE WHEN TO_DATE(Sales_Date) BETWEEN TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') - 10 AND TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
                    THEN 1 ELSE 0 END) OVER (PARTITION BY Vehicle_ID, TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY'))
                    Sales_Count_last_10,
      SUM(CASE WHEN TO_DATE(Sales_Date) BETWEEN TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') - 20 AND TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
                    THEN 1 ELSE 0 END) OVER (PARTITION BY Vehicle_ID, TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY'))
                    Sales_Count_last_10
FROM TABLE1

它只是为 Sales_Count、Sales_Count_last_10 和 Sales_Count_last_20 创建了相同的值。这是 N 天的列只是 Sales_Count 的重复

我还尝试了以下查询。

SELECT TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') Sales_Date1, Vehicle_ID, COUNT(*) SALES_COUNTS,
       SUM(CASE WHEN TO_DATE(Sales_Date) BETWEEN TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') - 10 AND TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
                    THEN 1 ELSE 0 END) Sales_Count_last_10,
       SUM(CASE WHEN TO_DATE(Sales_Date) BETWEEN TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY') - 20 AND TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')
                    THEN 1 ELSE 0 END) Sales_Count_last_20
FROM TABLE1
GROUP BY Vehicle_ID, TO_DATE(TO_CHAR(Sales_Date, 'MM/DD/YYYY'), 'MM/DD/YYYY')

它显示了与上一个查询相同的结果。复制 N-days 列的 Sales_Count。

请帮我解决这个问题。

标签： sqloraclegroup-bycountwindow-functions

解决方案

您可以使用 window 子句sum来指定要查看多少天。

，它包括从range between N preceding and current row当前行 - N 到当前行的所有值

这使：

with rws as (
  select 'X500' vehicle_id, to_date ( '01/03/2020 02:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
  select 'Y300' vehicle_id, to_date ( '01/11/2020 05:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
  select 'Q240' vehicle_id, to_date ( '01/25/2020 06:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
  select 'Q240' vehicle_id, to_date ( '01/25/2020 01:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
  select 'F310' vehicle_id, to_date ( '02/13/2020 06:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual union all
  select 'E990' vehicle_id, to_date ( '02/21/2020 02:00:00 PM', 'mm/dd/yyyy hh:mi:ss pm' ) sales_date from dual 
)  
  select distinct trunc ( sales_date ), vehicle_id, 
       count(*) over (
         partition by vehicle_id, trunc(sales_date) 
       )        sales_counts,
       count (*) over ( 
         order by trunc ( sales_date )
         range between 10 preceding and current row
       ) last_10,
       count (*) over ( 
         order by trunc ( sales_date )
         range between 20 preceding and current row
       ) last_20
from rws
order  by 1, 2

TRUNC(SALES_DATE)       VEHICLE_ID    SALES_COUNTS    LAST_10    LAST_20   
03-JAN-2020 00:00:00    X500                        1          1          1 
11-JAN-2020 00:00:00    Y300                        1          2          2 
25-JAN-2020 00:00:00    Q240                        2          2          3 
13-FEB-2020 00:00:00    F310                        1          1          3 
21-FEB-2020 00:00:00    E990                        1          2          2

PS，如果您想从 Oracle 数据库中的日期中删除时间组件，就trunc可以了。比to_date(to_char(..., 'fmt'))！

推荐阅读