时间戳

首页 > 解决方案 > 在给定时间范围内将结果转换为每小时计数

sql - 在给定时间范围内将结果转换为每小时计数

问题描述

我正在尝试编写一个查询以从表数据中获得以下结果：

由于我是初学者，我可以编写查询来获取数据的数据，但我不能将销售额分解为每小时数据。另外，我想将销售额与同一时间范围内的 YOY 和 WOW 数据进行比较。

这是提取当天数据的查询。

select COUNT(*)                               Order_Count_For_The_Day
       FROM
      purchase_Mobile
 WHERE
     CREATE_DATE between to_date('01/02/2018 00:00:00','mm/dd/yyyy hh24:mi:ss') and  to_date('01/02/2018 23:59:59','mm/dd/yyyy hh24:mi:ss')
      AND status_code >= 99;

有人可以更改查询并帮助我获得上述几点中提到的所需输出，我会非常感谢你的一生吗？

我已经尝试从给定表中提取数据，并且可以提取特定时间段的数据。

select COUNT(*)                               Order_Count_For_The_Day
       FROM
      purchase_Mobile
 WHERE
     CREATE_DATE between to_date('01/02/2018 00:00:00','mm/dd/yyyy hh24:mi:ss') and  to_date('01/02/2018 23:59:59','mm/dd/yyyy hh24:mi:ss')
      AND status_code >= 99;

标签： sqloracle

解决方案

为了获取每小时信息，您可以使用 datepart(HH,Create_Date)。然后使用每小时信息的 GROUP BY。 

SELECT COUNT(CREATE_DATE)           Order_Count_For_The_Day
    ,datepart(HH,CREATE_DATE)   Order_Hour
    ,MAX(CREATE_DATE)           CurrentDate
        FROM purchase_Mobile
        CREATE_DATE between to_date('01/02/2018 00:00:00','mm/dd/yyyy hh24:mi:ss') and  to_date('01/02/2018 23:59:59','mm/dd/yyyy hh24:mi:ss')
  AND status_code >= 99;

    GROUP BY datepart(HH,CREATE_DATE)

为了做今年，去年，其他时间框架 - 我将它们分成自己的临时表并最终加入它们。您需要为此调整一些问题（没有销售的时间，关闭的时间等，我如何做到这一点不会出现）但想让您有一些选择开始：

    DECLARE @StartDate  DateTime = '2018-1-2 00:00:00'
DECLARE @EndDate    DateTime = '2018-1-2 23:59:00'


DECLARE @CurrentResults Table
(OrderCount INT
,OrderHour INT
,CurrentDate DATETIME)
INSERT INTO @CurrentResults
SELECT COUNT(CREATE_DATE)           Order_Count_For_The_Day
        ,datepart(HH,CREATE_DATE)   Order_Hour
        ,MAX(CREATE_DATE)           CurrentDate
            FROM purchase_Mobile
            WHERE CREATE_DATE BETWEEN @StartDate AND @EndDate
                        AND status_code >= 99

        GROUP BY datepart(HH,CREATE_DATE)


OPTION (RECOMPILE);

DECLARE @LastYearResults Table
(LastYearOrderCount INT
,LastYearOrderHour INT
,LastYearDate DATETIME)
INSERT INTO @LYResults
SELECT COUNT(CREATE_DATE)           LastYearOrderCount
        ,datepart(HH,CREATE_DATE)   LastYearOrderHour
        ,MAX(CREATE_DATE)           LastYearDate
            FROM purchase_Mobile
            WHERE CREATE_DATE BETWEEN @StartDate-365 AND @EndDate-365
                        AND status_code >= 99
        GROUP BY datepart(HH,CREATE_DATE)

OPTION (RECOMPILE);

DECLARE @LastWeekResults Table
(LastWeekOrderCount INT
,LastWeekOrderHour INT
,LastWeekDate DATETIME)
INSERT INTO @LastWeekResults
SELECT COUNT(CREATE_DATE)           Order_Count
        ,datepart(HH,CREATE_DATE)   Order_Hour
        ,MAX(CREATE_DATE)           LastWeekDate
            FROM purchase_Mobile
            WHERE CREATE_DATE BETWEEN @StartDate-7 AND @EndDate-7
                        AND status_code >= 99
        GROUP BY datepart(HH,CREATE_DATE)

OPTION (RECOMPILE);

Select CR.OrderHour
        ,CR.OrderCount
        ,LY.LastYearOrderCount
        ,LW.LastWeekOrderCount
        ,CR.CurrentDate
        ,LY.LastYearDate
        ,LW.LastWeekDate
        FROM @CurrentResults As CR
            LEFT JOIN @LastYearResults AS LY ON LY.LastYearOrderHour = CR.OrderHour
            LEFT JOIN @LastWeekResults AS LW ON LW.LastWeekOrderHour = CR.OrderHour

        ORDER BY CR.OrderHour

推荐阅读