Azure应用洞察分析查询联合和列比较
本文关键字:比较 查询 应用 洞察 Azure | 更新日期: 2023-09-27 18:09:43
我有两个自定义事件A和B。A将有一行2列,说C和D(具有日期时间数据类型)。自定义事件B将具有具有2列E和F的行,其中E将具有日期时间值,F将是整数值。事件A和B没有公共列
现在我想让结果集包含那些在时间C和D之间发生的行。
例如,我在C栏-"2016-09-03"D栏-"2016-09-11"列E有多行,从"2016-08-01"到"2016-09-30"。我希望结果集只有列E和F值出现在列C和D之间的行
let tab1 = customEvents | extend cws = todatetime(tostring(customDimensions.['ColumnC'])) , cwe =todatetime(tostring(customDimensions.['ColumnB'])) | where name == "A" | project cws , cwe , name | limit 1 ;
let tab2 = customEvents | extend dt = todatetime(tostring(customDimensions.['E'])) | where name == "B" |summarize F=count(name) by E=startofday(dt) | order by E asc | project E , F ;
union tab* |take 10 |project cws , cwe , name , E , F
| where E > cws and E < cwe | project E , F
由于没有公共列,我尝试使用Union语句并合并两个表,但无法获得所需的结果集。任何关于这个问题的输入将对我非常有帮助。
当您想要连接两个没有公共列的数据集时,您可以创建一个虚拟列。试试这个查询:
customEvents
| where name == "A"
| extend cws = todatetime(tostring(customDimensions.['ColumnC'])) , cwe =todatetime(tostring(customDimensions.['ColumnB'])), dummy = "dummy" | project cws , cwe , name, dummy
| join kind = leftouter (
customEvents
| where name == "B"
| extend dt = todatetime(tostring(customDimensions.['E'])) | summarize F = count(name) by E=startofday(dt) | order by E asc | project E , F, dummy = "dummy"
) on dummy
| where E > cws and E < cwe | project E , F