Comprehensive Detailed Explanation
We need to query a KQL database table named Readings to generate output that includes both the current reading and the previous reading values and datetime for each row.
Step 1: Understanding Requirements
The output table shows:
City, Area, MeterReading, Datetime
PrevMeterReading, PrevDatetime
The prev() function in KQL is used to access the value of a column from the previous row (based on order).
Step 2: Query Construction
We start with filtering:
Readings
| where City == "Copenhagen"
| sort by Datetime
To add columns that calculate the previous reading and previous datetime, we use extend:
| extend PrevMeterReading = prev(MeterReading), PrevDatetime = prev(Datetime)
extend creates new columns.
prev() pulls the previous row’s values.
Finally, we only want specific columns in the output. For this, we use project:
| project City, Area, MeterReading, Datetime, PrevMeterReading, PrevDatetime
Step 3: Completed Query
Readings
| where City == "Copenhagen"
| sort by Datetime
| extend PrevMeterReading = prev(MeterReading), PrevDatetime = prev(Datetime)
| project City, Area, MeterReading, Datetime, PrevMeterReading, PrevDatetime
Why This is Correct
extend adds calculated columns.
prev() gives access to the previous row.
project selects only the required columns.
This matches exactly the table shown in the question.
References
Kusto Query Language - extend operator
Kusto Query Language - project operator
prev() function in KQL
