SQL查询以查找过去值的平均值 [英] Sql query to find averages of past values

问题描述

我有一些数据，可以整天按固定的小时间隔将值(卷)记录到oracle sql数据库中.我正在寻找一个SQL查询，该查询可以找到过去发生的平均数量，因此可以根据过去的时间将其插入到新表中.例如，对于我的数据集可以追溯到的每个星期三晚上7点，我想查找过去7点所有星期三当时的平均值，然后将平均值输出到新记录.然后找到周三晚上8点，晚上9点等的平均记录，直到一周中的所有7天都记录完整.

I have some data that logs a value (volume) in regular hourly intervals throughout the day to an oracle sql database. I am looking for an sql query that can find the average volume from occurrences in the past so I can insert that into a new table based on that time in the past. For example, for every Wednesday at 7pm for as long as my dataset goes back, I want to find what the average was at that time for all Wednesdays at 7pm in the past, and output the average to a new record. Then find the average recording for Wednesdays at 8pm , then 9pm and so forth, until all 7 days in a week are complete.

我主要不确定如何在sql中增加它.我认为我有一个查询，该查询将返回我想要的内容，但是我不确定如何增加值并插入.

I am primarily unsure of how to increment this in sql. I think I have a query that will return what I want, but I am unsure of how to increment the values and insert.

到目前为止，我在特定日期的特定时间里都有这个食物:

So far I have this for a particular hour of a particular day:

SELECT hour,day,AVG(volume)
FROM table
WHERE to_char(day, 'D') = 3 and hour = 19
GROUP BY hour,day;

推荐答案

如果我正确理解了您的问题，则希望对日期和小时的所有组合运行以上查询.

If i understand your question correctly, you want to run the above query for all combinations of day and hour.

此查询提供所有此类组合.

This query gives all such combinations.

  SELECT ds, LPAD (hrs, 2, '0') hrs
    FROM (    SELECT LEVEL ds
                FROM DUAL
          CONNECT BY LEVEL <= 7),
         (    SELECT LEVEL - 1 hrs
                FROM DUAL
          CONNECT BY LEVEL <= 24)
ORDER BY ds, hrs;

因此，您的查询应该是这样的.

So, your query should be something like this.

编辑:

INSERT INTO avg_table (days, hours, avrg)
   WITH xweek
        AS (SELECT ds, LPAD (hrs, 2, '0') hrs
              FROM (    SELECT LEVEL ds
                          FROM DUAL
                    CONNECT BY LEVEL <= 7),
                   (    SELECT LEVEL - 1 hrs
                          FROM DUAL
                    CONNECT BY LEVEL <= 24))
     SELECT t1.ds, t1.hrs, AVG (volume)
       FROM xweek t1, tables t2
      WHERE     t1.ds = TO_CHAR (t2.day(+), 'D')
            AND t1.hrs = t2.hour(+)
   GROUP BY t1.ds, t1.hrs;

我已经形成了xweek，它返回day和hour(24 * 7)的所有组合.外部与有问题的表连接，我得到所有组合的平均值.

I have formed xweek, which returns all combinations of day and hour(24 * 7). Outer joining this with table in question, i get avg values for all combinations.

这篇关于SQL查询以查找过去值的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！