查询返回特定日期范围内的平均记录数 [英] Query that returns Average number of records in a specific range of dates

问题描述

我有一个包含以下各列的表

I have a table with the following columns

OrderID     Number
ProductID   Number 
Order_date  Datetime
Qty         number

如何获取日期范围内每天平均下达的订单数.我想知道10月31日至11月4日之间每天的平均订单数.

How can I get average number of orders placed per day for a range of dates. I want to know the average orders placed per day between October 31st to November 4th both days included.

谢谢.

推荐答案

您的问题有点矛盾，它首先询问每天的平均订单数量，然后询问平均订单数量(这建议您要平均数量) ，而不是平均订单数)

Your question is a little contradictory, it fisrt asks for the average number of orders per day and then asks for the average orders (wich suggests you want the average quantity, not the average number of orders)

这应该为您指定范围的每一天平均提供数量(尽管要插入表名):

This should give you the averagy QTY for each day for the range you specify (insert your tablename though):

SELECT TRUNC(order_date) AS order_day,
       AVG(qty) AS daily_orders
  FROM <table>
 WHERE order_date 
          BETWEEN TO_DATE('31-OCT-2011 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
              AND TO_DATE('04-NOV-2011 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
 GROUP BY TRUNC(order_date);

如果您实际上是在询问您所参考的时间段内每天的平均订单数量(而不是平均数量)，则需要运行:

If you are actually asking for the average number of orders (not the average quantity) per day over the time period you referenced then you would need to run:

SELECT AVG(orders_per_day)
  FROM (
       SELECT TRUNC(order_date), count(*) AS orders_per_day
         FROM <table>
        WHERE order_date 
               BETWEEN TO_DATE('31-OCT-2011 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
                   AND TO_DATE('04-NOV-2011 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
        GROUP BY TRUNC(order_date)
       );

希望有帮助...

这篇关于查询返回特定日期范围内的平均记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！