Aide-mémoire SQL pour Langage de requête Kusto
Si vous êtes familiarisé avec SQL et que vous souhaitez apprendre KQL, convertissez les requêtes SQL en KQL en préfaçant la requête SQL avec une ligne de commentaire, --
et le mot clé explain
. La sortie montre la version KQL de la requête, ce qui peut vous aider à comprendre la syntaxe et les concepts KQL.
--
explain
SELECT COUNT_BIG(*) as C FROM StormEvents
Sortie
Requête |
---|
StormEvents<br> | summarize C=count()<br> | project C |
Aide-mémoire SQL vers Kusto
Le tableau suivant présente des exemples de requêtes dans SQL et leurs équivalents KQL.
Category | Requête SQL | Requête Kusto | En savoir plus |
---|---|---|---|
Sélectionnez les données d’une table | SELECT * FROM dependencies |
dependencies |
Instructions d’expression tabulaire |
-- | SELECT name, resultCode FROM dependencies |
dependencies | project name, resultCode |
project |
-- | SELECT TOP 100 * FROM dependencies |
dependencies | take 100 |
take |
Évaluation de la valeur null | SELECT * FROM dependencies WHERE resultCode IS NOT NULL |
dependencies | where isnotnull(resultCode) |
isnotnull() |
Opérateurs de comparaison (date) | 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 |
Opérateurs de comparaison (chaîne) | SELECT * FROM dependencies WHERE type = "Azure blob" |
dependencies | where type == "Azure blob" |
Opérateurs logiques |
-- | -- substring SELECT * FROM dependencies WHERE type like "%blob%" |
// substring dependencies | where type has "blob" |
has |
-- | -- wildcard SELECT * FROM dependencies WHERE type like "Azure%" |
// wildcard dependencies | where type startswith "Azure" // or dependencies | where type matches regex "^Azure.*" |
startswith correspond à regex |
Comparaison (booléenne) | SELECT * FROM dependencies WHERE !(success) |
dependencies | where success == False |
Opérateurs logiques |
Regroupement, agrégation | SELECT name, AVG(duration) FROM dependencies GROUP BY name |
dependencies | summarize avg(duration) by name |
Résumer avg() |
Distinct | SELECT DISTINCT name, type FROM dependencies |
dependencies | summarize by name, type |
Résumer Distinctes |
-- | 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() |
Alias de colonne, étendre | SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies GROUP BY name |
dependencies | summarize AvgD = avg(duration) by Name=operationName |
Alias, instruction |
-- | SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions |
ConferenceSessions | extend session=strcat(sessionid, " ", session_title) | project conference, session |
strcat() Projet |
Classement | SELECT name, timestamp FROM dependencies ORDER BY timestamp ASC |
dependencies | project name, timestamp | sort by timestamp asc nulls last |
sort |
N premiers par mesure | 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 |
top |
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)) |
|
Join | 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 |
join |
Requêtes imbriquées | 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 |
Résumer Où |
Contenu connexe
- Utiliser T-SQL pour interroger des données
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultezEnvoyer et afficher des commentaires pour