SQL Query Compare values in per 15 minutes and dis

问题:

I have a table with 2 columns. UTCTime and Values. The UTCTime is in 15 mins increment. I want a query that would compare the value to the previous value in one hour span and display a value between 0 and 4 depends on if the values are constant. In other words there is an entry for every 15 minute increment and the value can be constant so I just need to check each value to the previous one per hour.

For example

+---------|-------+
| UTCTime | Value |
------------------|
|   12:00 |  18.2 |
|   12:15 |  87.3 |
|   12:30 | 55.91 |
|   12:45 | 55.91 |
|    1:00 |  37.3 |
|    1:15 |  47.3 |
|    1:30 |  47.3 |
|    1:45 |  47.3 |
|    2:00 |  37.3 |
+---------|-------+

In this case, I just want a Query that would compare the 12:45 value to the 12:30 and 12:30 to 12:15 and so on. Since we are comparing in only one hour span then the constant values must be between 0 and 4 (O there is no constant values, 1 there is one like in the example above)

The query should display:

+----------+----------------+
| UTCTime  | ConstantValues |
----------------------------|
| 12:00    | 1              |
|  1:00    | 2              |
+----------|----------------+

I just wanted to mention that I am new to SQL programming. Thank you.

See SQL fiddle here

回答1:

Below is the query you need and a working solution Note: I changed the timeframe to 24 hrs

       ;with SourceData(HourTime, Value, RowNum)
  as
  (
    select 
      datepart(hh, UTCTime) HourTime, 
      Value, 
      row_number() over (partition by datepart(hh, UTCTime) order by UTCTime) RowNum
    from foo
    union 
    select 
        datepart(hh, UTCTime) - 1 HourTime, 
        Value,
        5
    from foo
    where datepart(mi, UTCTime) = 0
  )
  select cast(A.HourTime as varchar) + ':00' UTCTime, sum(case when A.Value = B.Value then 1 else 0 end) ConstantValues
  from SourceData A
   inner join SourceData B on A.HourTime = B.HourTime and
                           (B.RowNum = (A.RowNum - 1))
  group by cast(A.HourTime as varchar) + ':00'

回答2:

select SUBSTRING_INDEX(UTCTime,':',1) as time,value, count(*)-1 as total
from foo group by value,time having total >= 1;

fiddle

回答3:

Mine isn't much different from Vasanth's, same idea different approach.

The idea is that you need recursion to carry it out simply. You could also use the LEAD() function to look at rows ahead of your current row, but in this case that would require a big case statement to cover every outcome.

;WITH T
AS (
        SELECT a.UTCTime,b.VALUE,ROW_NUMBER() OVER(PARTITION BY a.UTCTime ORDER BY b.UTCTime DESC)'RowRank'
        FROM (SELECT * 
              FROM  #Table1 
              WHERE DATEPART(MINUTE,UTCTime) = 0
              )a
        JOIN #Table1 b
           ON b.UTCTIME BETWEEN a.UTCTIME AND DATEADD(hour,1,a.UTCTIME)
   )
 SELECT T.UTCTime, SUM(CASE WHEN T.Value = T2.Value THEN 1 ELSE 0 END)
 FROM T 
 JOIN T T2
   ON T.UTCTime = T2.UTCTime 
    AND T.RowRank = T2.RowRank -1
 GROUP BY T.UTCTime

If you run the portion inside the ;WITH T AS ( ) you'll see that gets us the hour we're looking at and the values in order by time. That is used in the recursive portion below by joining to itself and evaluating each row compared to the next row (hence the RowRank - 1) on the JOIN.