Grouping Annual Usages by Site ID & Service Type
General Explanation
What is this recipe?
This recipe is to group all of your total consumption usages by site and service type for a given year. Most of the example queries given here make sure that the start and end date of each statement is within the desired year, but you can include statements whose measurement periods partially overlap with the year as well.
Use case
What is this recipe for?
This recipe is for users who would like to see how much total utility usage (energy, gas, water, etc…) they’re using by site for a given year.
Snowflake SQL
What’s the snowflake SQL for this recipe?
What does the SQL for this recipe mean?
select site_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_usage_unit_of_measure) as unit_of_measure, service_type
from <YOUR_SNOWFLAKE_SHARE>.datahub_meters_by_statement_current
where year(period_end_date) = '2023'
and year(period_start_date) = '2023'
and standardized_measured_total_consumption_usage_value is not null
and site_id is not null
group by site_id, service_type
order by site_id;
The above query calculates your total consumption for a given site by service type for the 2023 calendar year.
If you have not completely mapped your meters to sites, you can also group by meter id like so:
select meter_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_usage_unit_of_measure) as unit_of_measure, service_type
from <YOUR_SNOWFLAKE_SHARE>.datahub_meters_by_statement_current
where year(period_end_date) = '2023'
and year(period_start_date) = '2023'
and standardized_measured_total_consumption_usage_value is not null
and meter_id is not null
group by meter_id, service_type
order by meter_id;
You also may want to order by total consumption to identify your sites that have the most usage:
select site_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_usage_unit_of_measure) as unit_of_measure, service_type
from <YOUR_SNOWFLAKE_SHARE>.datahub_meters_by_statement_current
where year(period_end_date) = '2023'
and year(period_start_date) = '2023'
and standardized_measured_total_consumption_usage_value is not null
and site_id is not null
group by site_id, service_type
order by total_consumption desc;
You also may want to look at total yearly consumption for a single service type:
select site_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_usage_unit_of_measure) as unit_of_measure, service_type
from <YOUR_SNOWFLAKE_SHARE>.datahub_meters_by_statement_current
where year(period_end_date) = '2023'
and year(period_start_date) = '2023'
and service_type = 'electric'
and standardized_measured_total_consumption_usage_value is not null
and site_id is not null
group by site_id, service_type
order by total_consumption desc;
If you want to include statements that straddle 2022 and 2023 or 2023 and 2024 you can update the query like this:
select site_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_usage_unit_of_measure) as unit_of_measure, service_type
from <YOUR_SNOWFLAKE_SHARE>.datahub_meters_by_statement_current
where (year(period_end_date) = '2023' or year(period_start_date) = '2023')
and standardized_measured_total_consumption_usage_value is not null
and site_id is not null
group by site_id, service_type
order by site_id;
You may also want to group by unit of measure if your statement data contains usages that cannot be standardized currently:
select site_id, round(sum(original_measured_total_consumption_usage_value), 2) as total_consumption, original_measured_total_consumption_usage_unit_of_measure as unit_of_measure, service_type
from <YOUR_SNOWFLAKE_SHARE>.datahub_meters_by_statement_current
where year(period_end_date) = '2023'
and year(period_start_date) = '2023'
and original_measured_total_consumption_usage_value is not null
and site_id is not null
group by site_id, unit_of_measure, service_type
order by site_id;
Return format
What’s the return format and an explanation of each returned field?
SITE_ID | TOTAL_CONSUMPTION | UNIT_OF_MEASURE | SERVICE_TYPE |
---|---|---|---|
1ECE16… | 412440.00 | kWh | electric |
1ECE89… | 502.00 | cubic_meters | natural gas |
1EDBF4… | 23188.00 | gallons | water |
Updated 6 months ago