DP-600 · Runbook

KQL cheat sheet

KQL pro lidi ze SQL a DAXu: operátory a funkce, které pokryjí většinu dotazů, s ekvivalenty

📡 Runbook · Real-Time Intelligence

Cíle kapitoly

Přeložit základní SQL a DAX konstrukce do KQL (where, project, summarize, join)
Pracovat s časovými řadami: ago, bin, make-series, anomálie
Použít window funkce, set operace a materialized views
Psát rychlé dotazy podle performance tipů a quick-start šablony

22.1

Úvod

Top 30 KQL operátorů a funkcí, které pokryjí 90% real-world queries. Cíl: PBI/DAX/SQL člověk rychle pochopí KQL bez učení od nuly.

KQL = Kusto Query Language. Pipeline-based syntax (| = "then"). Read top-down.

22.2

Filtering & projection

KQL `where` ↔ SQL `WHERE`, DAX `FILTER`

KQL

b_chmi_clean
| where pollutant_code == "PM10"
| where measurement_ts > ago(24h)

SQL

SELECT * FROM b_chmi_clean
WHERE pollutant_code = 'PM10'
  AND measurement_ts > DATEADD(hour, -24, GETDATE())

DAX

FILTER(b_chmi_clean,
       b_chmi_clean[pollutant_code] = "PM10"
       && b_chmi_clean[measurement_ts] > NOW() - 1)

KQL `project` ↔ SQL `SELECT col1, col2`

KQL

b_chmi_clean | project measurement_ts, station_nazev, value

KQL `extend` — přidá computed column (immutable, neovlivní existing)

KQL

b_chmi_clean
| extend over_limit = (value > 50)

DAX equivalent: ADDCOLUMNS(table, "over_limit", value > 50).

KQL `project-away`, `project-rename`, `project-reorder`

KQL

b_chmi_clean
| project-away ingest_ts                  // drop column
| project-rename mereni = measurement_ts  // rename
| project-reorder station_nazev, value, mereni  // reorder

22.3

Aggregation

KQL `summarize` ↔ SQL `GROUP BY`, DAX `SUMMARIZE`

KQL

b_chmi_clean
| summarize avg(value), count() by pollutant_code

Výsledné sloupce: avg_value, count_, pollutant_code (auto-named). Custom names:

KQL

b_chmi_clean
| summarize avg_val = avg(value), n = count() by pollutant_code

Time bucketing — `bin()` ↔ SQL `DATE_TRUNC`, DAX `time intelligence`

KQL

b_chmi_clean
| where measurement_ts > ago(24h)
| summarize avg(value) by bin(measurement_ts, 1h), pollutant_code

bin(col, step): step = 1h, 15m, 1d, 7d, ...

Aggregation funkce

count() — row count
dcount(col) — distinct count (HyperLogLog approx, fast)
dcount_hll(col) — HLL state object (mergeable)
sum(col), avg(col), min(col), max(col)
stdev(col), variance(col)
percentiles(col, 50, 90, 95, 99)
make_set(col) — array of distinct values
make_list(col) — array including duplicates
arg_max(rank_col, *) — row with max rank_col, return all columns
arg_min(rank_col, *)
take_any(col) — sample 1 value (fast, non-deterministic)

22.4

Joins

KQL `join` ↔ SQL `JOIN`, DAX `RELATED`

KQL

b_chmi_clean
| join kind=inner (
    dim_station
) on $left.idRegistration == $right.id_registration

kind= možnosti:

inner (default) — match v obou
leftouter — všechny levé + match z pravé
rightouter — všechny pravé + match z levé
fullouter — všechny obě
leftanti — levé bez match v pravé (perfect pro "missing" detection)
rightanti — opačně
leftsemi — levé pokud má match (fast filter)
rightsemi

Time-window join — `join` na time range

KQL

let alerts = T_alerts | project alert_ts, station_id;
let measurements = T_measurements | project measurement_ts, station_id, value;
alerts
| join kind=inner (measurements) on station_id
| where measurement_ts between (alert_ts .. alert_ts + 1h)

22.5

Time series

`ago()` — relativní time

KQL

b_chmi_clean | where measurement_ts > ago(24h)
b_chmi_clean | where measurement_ts between (ago(2h) .. ago(1h))

ago(timespan): 1h, 30m, 7d, 1d, 30s.

`make-series` — equispaced time series with gap filling

KQL

b_chmi_clean
| where pollutant_code == "PM10"
| make-series val = avg(value) on measurement_ts step 1h by idRegistration

make-series vrací JEDNU row per group s arrays val a measurement_ts of equal length. Hodí se na series_* funkce.

`series_decompose_anomalies` — anomaly detection

KQL

b_chmi_clean
| where pollutant_code == "PM10"
| make-series val = avg(value) on measurement_ts step 1h
| extend (anomalies, score, baseline) = series_decompose_anomalies(val, 2.0)
| mv-expand val to typeof(real),
            measurement_ts to typeof(datetime),
            anomalies to typeof(int)
| where anomalies != 0

`mv-expand` — array → rows

KQL

T | extend tags = dynamic(["a", "b", "c"])
  | mv-expand tags
// Returns 3 rows per original row

DAX equivalent: composite table generation, awkward.

22.6

String functions

Common

strlen(s) — length
tolower(s), toupper(s)
substring(s, start, length)
split(s, sep) → array
strcat(a, b, c, ...) — concat
replace_string(s, old, new)
extract(regex, capture_idx, source) — first match
extract_all(regex, source) — all matches → array

Pattern matching

KQL

T | where col matches regex @"^[A-Z]{3}-\d+$"  // regex
T | where col contains "foo"                    // substring
T | where col startswith "PID_"
T | where col endswith ".csv"
T | where col has "exact_word"                  // word match (faster)

has is much faster than contains (uses term index). Always prefer has if you can.

22.7

Conditional logic

KQL `case()` ↔ SQL `CASE WHEN`, DAX `SWITCH`

KQL

| extend speed_class = case(
    speed_kmh < 30, "stopped",
    speed_kmh < 60, "slow",
    speed_kmh < 90, "normal",
    "fast"
  )

`iif` — single ternary

KQL

| extend over_limit = iif(value > eu_limit, true, false)
// shorthand:
| extend over_limit = (value > eu_limit)

`coalesce(a, b, c)` — first non-null

KQL

| extend pollutant_name = coalesce(pollutant_name, idValueType, "UNKNOWN")

22.8

Top / sort

KQL

T | top 10 by some_col desc
T | sort by some_col asc, other_col desc
T | take 100   // any 100 rows (fast, no order)

top is NOT same as take — top orders, take doesn't.

22.9

Window functions

`prev()`, `next()`, `row_number()` — needs `serialize` first

KQL

T
| where vehicle_id == "BUS_1234"
| sort by timestamp asc
| serialize
| extend prev_lat = prev(lat),
         delta_lat = lat - prev(lat),
         row_num = row_number()

serialize operator je nutný pre window functions (KQL je default ne-ordered).

22.10

Set operations

KQL

T1 | union T2          // SQL UNION ALL (no dedup)
T1 | union kind=outer T2   // outer union (handles different schemas)
T1 | join kind=inner T2 on key       // ~ SQL INTERSECT (KQL nema intersect)
T1 | join kind=leftanti T2 on key    // ~ SQL EXCEPT, T1 minus T2 (KQL nema except)

22.11

Materialized views (read pattern)

KQL

mv_b_chmi_last_per_station
| where pollutant_code == "PM10"
| top 10 by value desc

Same query syntax as regular table. The MV evaluates incrementally as new data comes in (zero query-time cost compared to re-aggregating raw).

22.12

Performance tips

Tip	Reason
Filter `where` early in pipeline	Reduce data passed to expensive ops
Use `has`/`has_cs` instead of `contains`	Term index, much faster
`summarize` before `join` if possible	Reduce join input cardinality
Use `dcount` not `count(distinct ...)`	HLL is 10-100× faster
`take` instead of `top` if you don't care about order	No sort cost
Materialized views for hot aggregations	Pre-computed, < 100ms query
Avoid `extend` in hot paths if possible	Each extend = new column, more memory
Use `project` early to drop unused columns	Less data to carry through pipeline
`let` for repeated subqueries	Compute once, use multiple times

22.13

KQL ↔ DAX ↔ SQL quick reference

Concept	KQL	SQL	DAX
Filter	`where`	`WHERE`	`FILTER`
Project	`project`	`SELECT`	`SELECTCOLUMNS`
Add column	`extend`	`SELECT col, expr AS new`	`ADDCOLUMNS`
Group by	`summarize ... by`	`GROUP BY`	`SUMMARIZE`
Sort	`sort by` / `order by`	`ORDER BY`	`SORTBY`
Top N	`top 10 by`	`TOP 10`	`TOPN`
Distinct	`distinct`	`SELECT DISTINCT`	`DISTINCT`
Count distinct	`dcount`	`COUNT(DISTINCT ...)`	`DISTINCTCOUNT`
Join	`join`	`JOIN`	`RELATED` / `RELATEDTABLE`
Time bin	`bin(ts, 1h)`	`DATE_TRUNC('hour', ts)`	`STARTOFHOUR` etc
Last per group	`summarize arg_max(ts, *) by g`	windowed `ROW_NUMBER`+filter	`MAXX` per group
Pivot	`evaluate pivot(...)`	`PIVOT (...)`	matrix visual / `SWITCH`
Anomaly detection	`series_decompose_anomalies`	n/a	n/a

22.14

Quick start template (copy-paste)

KQL

let WINDOW = 24h;
let POLLUTANT = "PM10";
let LIMIT = 50.0;

b_chmi_clean
| where measurement_ts > ago(WINDOW)
| where pollutant_code == POLLUTANT
| extend over_limit = (value > LIMIT)
| summarize 
    n_readings = count(),
    n_breaches = countif(over_limit),
    avg_val = avg(value),
    p95_val = percentile(value, 95),
    last_breach = arg_max(over_limit, *)
    by station_nazev, kraj_kod
| extend pct_breach = round(100.0 * n_breaches / n_readings, 1)
| top 10 by pct_breach desc

22.15

Reference

KQL language: https://learn.microsoft.com/kusto/query/
KQL cheat sheet (Microsoft): KQL quick reference
KQL ↔ SQL: SQL to Kusto cheat sheet
Time series functions: https://learn.microsoft.com/kusto/query/series-stats-function

KQL cheat sheet

Cíle kapitoly

Úvod

Filtering & projection

KQL where ↔ SQL WHERE, DAX FILTER

KQL project ↔ SQL SELECT col1, col2

KQL extend — přidá computed column (immutable, neovlivní existing)

KQL project-away, project-rename, project-reorder