将 SQL 转换为 Kusto 查询语言的速查表

项目
09/24/2024
适用于：✅ Microsoft Fabric✅ Azure 数据资源管理器Azure Monitor ✅ Microsoft ✅ Sentinel

如果你熟悉 SQL 并想要了解 KQL，请将 SQL 查询转换为 KQL，方法是在 SQL 查询前面加上注释行 -- 和关键字 explain 。输出将显示该查询的 KQL 版本，这可以帮助你了解 KQL 语法和概念。
explain SELECT COUNT_BIG(*) as C FROM StormEvents Null 评估 SELECT * FROM dependencies
WHERE resultCode IS NOT NULL dependencies
| where isnotnull(resultCode) isnotnull() 比较运算符（日期） SELECT * FROM dependencies
WHERE timestamp > getdate()-1 dependencies
| where timestamp > ago(1d) ago() SELECT * FROM dependencies
WHERE timestamp BETWEEN ... AND ... dependencies
| where timestamp between (datetime(2016-10-01) .. datetime(2016-11-01)) between 比较运算符（字符串） SELECT * FROM dependencies
WHERE type = "Azure blob" dependencies
| where type == "Azure blob" 逻辑运算符 -- substring
SELECT * FROM dependencies
WHERE type like "%blob%" // substring
dependencies
| where type has "blob" -- wildcard
SELECT * FROM dependencies
WHERE type like "Azure%" // wildcard
dependencies
| where type startswith "Azure"
// or
dependencies
| where type matches regex "^Azure.*" startswith
matches regex 比较（布尔值） SELECT * FROM dependencies
WHERE !(success) dependencies
| where success == False 逻辑运算符分组，聚合 SELECT name, AVG(duration) FROM dependencies
GROUP BY name dependencies
| summarize avg(duration) by name summarize
avg() Distinct SELECT DISTINCT name, type FROM dependencies dependencies
| summarize by name, type summarize
distinct SELECT name, COUNT(DISTINCT type)
FROM dependencies
GROUP BY name dependencies
| summarize by name, type | summarize count() by name
// or approximate for large sets
dependencies
| summarize dcount(type) by name count()
dcount() 列别名、扩展 SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies
GROUP BY name dependencies
| summarize AvgD = avg(duration) by Name=operationName Alias 语句 SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions ConferenceSessions
| extend session=strcat(sessionid, " ", session_title)
| project conference, session strcat()
project 中间件排序 SELECT name, timestamp FROM dependencies
ORDER BY timestamp ASC dependencies
| project name, timestamp
| sort by timestamp asc nulls last 按度量值排名的前 n 位 SELECT TOP 100 name, COUNT(*) as Count FROM dependencies
GROUP BY name
ORDER BY Count DESC dependencies
| summarize Count = count() by name
| top 100 by Count desc Union SELECT * FROM dependencies
UNION
SELECT * FROM exceptions union dependencies, exceptions union SELECT * FROM dependencies
WHERE timestamp > ...
UNION
SELECT * FROM exceptions
WHERE timestamp > ... dependencies
| where timestamp > ago(1d)
| union
(exceptions
| where timestamp > ago(1d)) SELECT * FROM dependencies
LEFT OUTER JOIN exceptions
ON dependencies.operation_Id = exceptions.operation_Id dependencies
| join kind = leftouter
(exceptions)
on $left.operation_Id == $right.operation_Id SELECT * FROM dependencies
WHERE resultCode ==
(SELECT TOP 1 resultCode FROM dependencies
WHERE resultId = 7
ORDER BY timestamp DESC) dependencies
| where resultCode == toscalar(
dependencies
| where resultId == 7
| top 1 by timestamp desc
| project resultCode) toscalar Having SELECT COUNT(\*) FROM dependencies
GROUP BY name
HAVING COUNT(\*) > 3 dependencies
| summarize Count = count() by name
| where Count > 3 summarize
where
使用 T-SQL 查询数据