Scenario 10: Get summary statistics when a certain condition applies

You can use SliceBy to answer questions like:

• How much energy was consumed per batch?

• What was the average flow rate when a valve was open?

• What was lowest pressure today while a pump was running?

Using the SliceBy Parameter

SliceBy lets you define batches to be used with an Analog Summary query to get averages by batch. When you also use SliceByValue, you can get summary information per batch when a certain condition exists.

Example: Finding Total Energy Consumed per Batch

This query finds the total energy consumed per batch.

select SliceBy, SliceByValue, TagName, MIN(StartDateTime) as PeriodStart,

MAX(EndDateTime) as PeriodEnd, SUM(wwResolution)/1000 as [Duration(Sec)],

MIN(Minimum) as [Min], MAX(Maximum) as [Max], SUM(Integral) as [Total]

from AnalogSummaryHistory

where TagName='PowerMeter'

and SliceBy='R31.BatchNum'

and StartDateTime>='2019-04-17 0:00'

and EndDateTime<='2019-04-17 2:00'

group by TagName, SliceBy, SliceByValue

The results are:

Example: Finding Average Flow Rate When Valve is Open

This query retrieves statistics on the flow rate, but only while the valve is open. The query finds an average of weighted averages.

First, the query defines a batch by instance that the valve is open. Then, it calculates the total volume of the flow for that batch and calculates a weighted average by duration. Finally, it calculates an "average of averages" for the batch.

select SliceBy, SliceByValue, TagName, MIN(StartDateTime) as PeriodStart,

MAX(EndDateTime) as PeriodEnd, SUM(wwResolution)/1000 as [Duration(Sec)], MAX(Maximum) as [Max],

SUM(Integral) as [Total], AVG=SUM(Average*wwResolution)/SUM(wwResolution)

from AnalogSummaryHistory

where TagName='WaterFlow'

and SliceBy='R31_PLC.WaterValve'

and SliceByValue = 1 -- Valve Open

and StartDateTime>='2019-04-17 0:00'

and EndDateTime<='2019-04-17 2:00'

group by TagName, SliceBy, SliceByValue

The results are:

Note: This example correctly calculates the overall average for each state in the "Average" column by weighting the duration of each state. As explained by Simpson’s Paradox, the simpler, "AvgOfAvg" calculation is not statistically accurate and can differ significantly with some data sets.

Example: Finding the Lowest Pressure Today While a Pump Was Running

This example finds today's lowest pressure reading for a pump while it was running.

declare @date datetime

set @date = convert(date, getdate())

select SliceBy, SliceByValue, TagName, MIN(StartDateTime) as PeriodStart,

MAX(EndDateTime) as PeriodEnd, SUM(wwResolution)/1000 as [Duration(Sec)], MIN(Minimum) [Min],

MAX(Maximum) as [Max]

from AnalogSummaryHistory

where TagName='LinePressure'

and SliceBy='StorageTank_001.OutletValve'

and SliceByValue = 1 -- Valve Open

and StartDateTime>= @date

group by TagName, SliceBy, SliceByValue

Where results might be: