CODEWITHSUNDEEP
sqlsql-servert-sql
Beginner
Beginner

Reputation: 79

Get Average, Max, Min , Last added value and Time

I have a Parameters Log table like this:

Log ID Temperature Humidity country Date
1 40 10 USA 2021-09-04 10:00:51.737
2 20 20 USA 2021-09-04 11:09:51.737
3 30 30 USA 2021-09-04 12:09:51.737

The above table stores Temperature, Humidity likewise different parameters for the different countries at different times. I want an average of This parameter, max, min, last added value, and Last added Time for a specific day.

For example:

Parameters Country Average Min Max Last value Last updated time
Temperature USA 30 20 40 30 2021-09-04 12:09:51.737
Humidity USA 20 10 30 30 2021-09-04 12:09:51.737

This is what I did for Temperature :

;WITH CTE AS
(
    SELECT  
        AVG(Temperature) AVG_Temp,
        MIN(Temperature) MIN_Temp,
        MAX(Temperature) Max_Temp, 
        Country  
    FROM
        Parameters_Log 
    GROUP BY
        Country 
    WHERE 
        Country = 'USA' 
        AND CAST([date] AS date) = '2021-09-04' 
)

First I am getting avg, Min, and Max for temperature for a country as above

SELECT TOP 1 
    c.Country, 
    c.AVG_Temp, 
    c.MIN_Temp,
    c.Max_Temp,
    [log].date,
    [log].Temperature 
FROM
    Parameters_Log [log] 
INNER JOIN
    cte c ON c.country = [log].country 
          AND 
WHERE
    [log].Country = 'USA' 
    AND cast([log].[date] AS date) = '2021-09-04'
ORDER BY 
    [log].[date] DESC

Then I am using order by date desc to get the last added Temperature value and time. Finally, I am Taking Top 1 and store in a temporary table then the same procedure following for Humidity.

Can anyone guide me on what will be the performance-wise best approach?

Upvotes: 1

Views: 310

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can unpivot the values using apply. Then use row_number() and aggregation for the summary:

select country, parameter,
       avg(val), min(val), max(val),
       max(case when seqnum = 1 then val end) as last_val,
       max(date)
from (select pl.country, pl.date, v.*,
             row_number() over (partition by pl.country, v.parameter order by pl.date desc) as seqnum
      from parameters_log pl cross apply
           (values ('Temperature', pl.temperature), ('Humidity', pl.humidity)
           ) v(parameter, val)
     ) pl
group by country, parameter;

If you want a specific day, add a where clause to the subquery, such as:

where pl.date >= '2021-09-04' and pl.date < '2021-09-05'

Here is a db<>fiddle.

Upvotes: 3

Rahul Biswas
Rahul Biswas

Reputation: 3467

Please try this. last value is picked based on max logid. apply cross join for whole result set into 2 copies.

-- SQL SERVER
SELECT CASE WHEN id = 1 THEN 'Temperature' ELSE 'Humidity' END Parameters
     , pl.country
     , CASE WHEN id = 1 THEN avg_temp ELSE avg_hum END Average
     , CASE WHEN id = 1 THEN min_temp ELSE min_hum END "Min"
     , CASE WHEN id = 1 THEN max_temp ELSE max_hum END "Max"
     , CASE WHEN id = 1 THEN pl.temparature ELSE pl.humidity END last_value
     , t.last_date "Last updated time"
FROM Parameters_Log pl
INNER JOIN (SELECT country
                 , MIN(temparature) min_temp
                 , MAX(temparature) max_temp
                 , AVG(temparature) avg_temp
                 , MIN(humidity) min_hum
                 , MAX(humidity) max_hum
                 , AVG(humidity) avg_hum
                 , MAX(logid) max_id
                 , MAX(tdate) last_date
            FROM Parameters_Log
            WHERE CAST(tdate AS date) = '2021-09-04'
            GROUP BY country) t
       ON pl.logid = t.max_id
CROSS JOIN (SELECT 1 id UNION SELECT 2 id) c

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=48e671202de62aeff8ea737ba4d1ba5a

If desired output depends on max_date then ON condition will

ON pl.tdate = t.last_date

If desired output depends on max log_id then ON condition will

 ON pl.logid = t.max_id

Upvotes: 0

Related Questions