Skip to content

Instantly share code, notes, and snippets.

@thebapi

thebapi/gist:9bc8a48052ed80d4409d3750ec210afd

Last active July 31, 2023 14:59
Show Gist options
  • Save thebapi/9bc8a48052ed80d4409d3750ec210afd to your computer and use it in GitHub Desktop.
Save thebapi/9bc8a48052ed80d4409d3750ec210afd to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
with performance_table
as (select name,
asset_uuid,
device_uuid,
group_uuid,
value,
time
from public.messages
where time between 1690653600000 and 1690739999000
and asset_uuid = '1e112e32-a533-48a7-9c1d-ef30c8b4ed6e'
and device_uuid = 'f356f491-df0d-4ba9-9e3d-218c55ac71cf'
and group_uuid = '2315d557-ed55-4632-85c8-876607240130'
and name = $$temperature1$$)
select asset_uuid,
device_uuid,
group_uuid,
name as property_name,
$$Bradford Dawson's Unit$$ as property_group_name,
sum(case
when value >= 0 and value <= 1
then 1
else 0
end) as optimal_count,
sum(case
when value < 0 and
value >= -1 and
value <= 2
then 1
else 0
end) as optimal_below_low_count,
sum(case
when value > 1 and
value >= -1 and
value <= 2
then 1
else 0 end) as optimal_above_high_count,
sum(case
when value < -1
then 1
else 0 end) as incident_below_low_count,
sum(case
when value > 2
then 1
else 0 end) as incident_above_high_count,
count(*) as total_count
from performance_table
group by asset_uuid, device_uuid, group_uuid, name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment