我需要得到一张订单表 [英] I need to get an order table as a result

问题描述

我有一个这样的订单表。该表包含在特定日期上午12点之后下单的订单。



Ordername订购已完成订单



A 00 ：12：56 01:24:42

B 01:15:20 01:45:30



我需要计算一下每1分钟间隔处理的订单数量。

仅在订单处理时，计数应为1或更多。其余时间应为0





我需要以下表格的结果： -



Ordertime count（orders）

00:00 0

00:01 0

'

'

00:12 0

00:13 1

00:14 1

'

'

01:16 2

01:17 2

'

01:25 1

01:26 1

'

01:46 0



'

直到查询运行的当前时间。





请提供合适的SQL（或PL / SQL）解决方案。



我尝试了什么：



我尝试过使用union select但是没有给出我想要的结果。

解决方案

假设你想要创建一个能够计算一天中每分钟订单数量的查询，我必须警告你这样的查询消耗了大量的时间和资源...... br $>


步骤：

1. 创建 TimeTable 存储当天的每小时和每分钟（从' 00:00 '到' 00:59 '）。
   
   

     USE  yourDatabaseName; 
    
     CREATE   TABLE  TimeTable 
   （
    StartTime < span class =code-keyword> TIME  
   ）; 

   
   
   这应该只进行一次！
   
   要用数据填写此表，请使用以下查询：
   
   

   ;  WITH  CteTimeTable  AS  
   （
     SELECT   CONVERT （ TIME ，'  00： 00:00'） AS  StartTime， CONVERT （ TIME ，'  23:59:00'） AS  EndTime 
     UNION   ALL  
     SELEC T  DATEADD（N， 1 ，StartTime）， CONVERT （ TIME ，'  23:59:00'） AS  EndTime 
     FROM  CteTimeTable 
     WHERE  DATEADD（N， 1 ，StartTime）< = EndTime 
   ）
     INSERT   INTO  TimeTable（StartTime）
     SELECT  StartTime 
     FROM  CteTimeTable 
     OPTION （MAXRECURSION  0 ）

   
   
   

2. 现在，你应该使用Common Table Expressions（参见下面的链接）将订单分成几分钟。
   
   试试这个：< pre lang =SQL>; WITH OrigData AS
   （
   SELECT 1 AS CntOfOrder，Ordername， CONVERT （ TIME ， LEFT （Orderplaced， 5 ）） AS StartTime， CONVERT （ TIME ， LEFT （订单已完成， 5 ）） As EndTime
   FROM YourTable
   UNION ALL
   SELECT CntOfOrder，Ordername，DATEADD（N， 1 ，StartTime） AS StartTime，EndTime
   FROM OrigData
   WHERE DATEADD（N， 1 ，StartTime）< = EndTime
   ）
   SELECT StartTime，SUM（CntOfOrder） AS NoOfOrders
   FROM OrigData
   GROUP BY StartTime

   
   
   以上查询返回每个订单每分钟的订单总和，例如：
   
   

    .... 
    01：08：00.0000000 1 
    01：09：00.0000000 1 
    01：10：00.0000000 1 
    01：11：00.0000000 1 
    01：12：00.0000000 1 
    01：13：00.0000000 1 
    01：14：00.0000000 1 
    01：15：00.0000000 2 
    01：16：00.0000000 2 
    01：17：00.0000000 2 
    .... 

   
   
   

3. 最后，你必须加入两个表。因此，用以下代码替换最后的 SELECT 语句：
   
   

     SELECT  L.StartTime，SUM（R.CntOfOrder）
     FROM  TimeTable  AS  L < span class =code-keyword> LEFT   JOIN  OrigData  AS  R  ON  L.StartTime = R.StartTime 
     GROUP   BY  L.StartTime 

   
   
   

欲了解更多详情，请参阅：

使用公用表格表达式 [ ^ ]

WITH common_table_expr ession（Transact-SQL）| Microsoft Docs [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

I have an order table that goes this way. The table contains orders placed after 12 am on a particular day.

Ordername Orderplaced Ordercompleted

A 00:12:56 01:24:42
B 01:15:20 01:45:30

I need to get the count of the orders under process for every 1 minute interval.
The count should be 1 or more only when the order is in process.It should be 0 for rest of the time


I need the result in this form :-

Ordertime count(orders)
00:00 0
00:01 0
'
'
00:12 0
00:13 1
00:14 1
'
'
01:16 2
01:17 2
'
01:25 1
01:26 1
'
01:46 0

'
up until the present time when the query is run.


Please provide a suitable SQL (or PL/SQL) solution.

What I have tried:

I have tried using union select but that does not give me the desired result .

解决方案

Assuming that you want to create a query to able to count number of orders in every minute in a day, i have to warn you that such of query consumes a lot of time and resources...

Steps to do:

1. Create a TimeTable to store every hour and minute of the day (from '00:00' to '00:59').
   

   USE yourDatabaseName;
   
   CREATE TABLE TimeTable
   (
       StartTime TIME
   );

   
   This should be done only once!
   To full fill this table with data, use below query:
   

   ;WITH CteTimeTable AS
   (
   	SELECT CONVERT(TIME, '00:00:00') AS StartTime, CONVERT(TIME, '23:59:00') AS EndTime
   	UNION ALL
   	SELECT DATEADD(N, 1, StartTime), CONVERT(TIME, '23:59:00') AS EndTime
   	FROM CteTimeTable
   	WHERE DATEADD(N, 1, StartTime) <=EndTime 
   )
   INSERT INTO TimeTable(StartTime)
   SELECT StartTime 
   FROM CteTimeTable
   OPTION (MAXRECURSION 0)

   
   

2. Now, you should use Common Table Expressions (refer below links) to split orders into minutes.
   Try this:

   ;WITH OrigData AS
   (
   	SELECT 1 AS CntOfOrder, Ordername, CONVERT(TIME, LEFT(Orderplaced, 5)) AS StartTime, CONVERT(TIME, LEFT(Ordercompleted, 5)) As EndTime
   	FROM YourTable
   	UNION ALL
   	SELECT CntOfOrder, Ordername, DATEADD(N, 1, StartTime)  AS StartTime, EndTime
   	FROM OrigData 
   	WHERE DATEADD(N, 1, StartTime) <= EndTime 
   )
   SELECT StartTime, SUM(CntOfOrder) AS NoOfOrders
   FROM OrigData 
   GROUP BY StartTime

   
   Above query returns sum of orders for each minute of every order, for example:
   

   ....
   01:08:00.0000000	1
   01:09:00.0000000	1
   01:10:00.0000000	1
   01:11:00.0000000	1
   01:12:00.0000000	1
   01:13:00.0000000	1
   01:14:00.0000000	1
   01:15:00.0000000	2
   01:16:00.0000000	2
   01:17:00.0000000	2
   ....

   
   

3. Finally, you have to join both tables. So, replace last SELECT statement with this:
   

   SELECT L.StartTime, SUM(R.CntOfOrder)
   FROM TimeTable AS L LEFT JOIN OrigData AS R ON L.StartTime = R.StartTime
   GROUP BY L.StartTime

   
   

For further details, please see:
Using Common Table Expressions[^]
WITH common_table_expression (Transact-SQL) | Microsoft Docs[^]
Recursive Queries Using Common Table Expressions[^]

这篇关于我需要得到一张订单表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！