in 和 !in 运算符in and !in operators
根据提供的值集筛选记录集。Filters a record set based on the provided set of values.
Table1 | where col in ('value1', 'value2')
备注
- 向运算符添加“~”会使值的搜索不区分大小写:
x in~ (expression)
或x !in~ (expression)
。Adding '~' to the operator makes values' search case-insensitive:x in~ (expression)
orx !in~ (expression)
. - 在表格表达式中,会选择结果集的第一列。In tabular expressions, the first column of the result set is selected.
- 表达式列表最多可生成
1,000,000
个值。The expression list can produce up to1,000,000
values. - 嵌套数组将平展为单个值列表。Nested arrays are flattened into a single list of values. 例如,
x in (dynamic([1,[2,3]]))
重命名为x in (1,2,3)
。For example,x in (dynamic([1,[2,3]]))
becomesx in (1,2,3)
.
语法Syntax
区分大小写的语法Case-sensitive syntax
T |
where
col in
(
list of scalar expressions)
T |
where
col in
(
list of scalar expressions)
T |
where
col in
(
tabular expression)
T |
where
col in
(
tabular expression)
T |
where
col !in
(
list of scalar expressions)
T |
where
col !in
(
list of scalar expressions)
T |
where
col !in
(
tabular expression)
T |
where
col !in
(
tabular expression)
不区分大小写的语法Case insensitive syntax
T |
where
col in~
(
list of scalar expressions)
T |
where
col in~
(
list of scalar expressions)
T |
where
col in~
(
tabular expression)
T |
where
col in~
(
tabular expression)
T |
where
col !in~
(
list of scalar expressions)
T |
where
col !in~
(
list of scalar expressions)
T |
where
col !in~
(
tabular expression)
T |
where
col !in~
(
tabular expression)
参数Arguments
- T:其记录待筛选的表格输入。T - The tabular input whose records are to be filtered.
- col - 要筛选的列。col - The column to filter.
- list of expressions - 以逗号分隔的表格、标量或文本表达式的列表。list of expressions - A comma-separated list of tabular, scalar, or literal expressions.
- tabular expression - 包含一组值的表格表达式。tabular expression - A tabular expression that has a set of values. 如果表达式包含多个列,则使用第一列。If the expression has multiple columns, the first column is used.
返回Returns
其谓词为 true
的 T 中的行。Rows in T for which the predicate is true
.
示例Examples
使用“in”运算符Use 'in' operator
StormEvents
| where State in ("FLORIDA", "GEORGIA", "NEW YORK")
| count
计数Count |
---|
47754775 |
使用“in~”运算符Use 'in~' operator
StormEvents
| where State in~ ("Florida", "Georgia", "New York")
| count
计数Count |
---|
47754775 |
使用“!in”运算符Use '!in' operator
StormEvents
| where State !in ("FLORIDA", "GEORGIA", "NEW YORK")
| count
计数Count |
---|
5429154291 |
使用动态数组Use dynamic array
let states = dynamic(['FLORIDA', 'ATLANTIC SOUTH', 'GEORGIA']);
StormEvents
| where State in (states)
| count
计数Count |
---|
32183218 |
子查询Subquery
// Using subquery
let Top_5_States =
StormEvents
| summarize count() by State
| top 5 by count_;
StormEvents
| where State in (Top_5_States)
| count
同一查询可以编写为:The same query can be written as:
// Inline subquery
StormEvents
| where State in (
( StormEvents
| summarize count() by State
| top 5 by count_ )
)
| count
计数Count |
---|
1424214242 |
其他示例中的 TopTop with other example
let Lightning_By_State = materialize(StormEvents | summarize lightning_events = countif(EventType == 'Lightning') by State);
let Top_5_States = Lightning_By_State | top 5 by lightning_events | project State;
Lightning_By_State
| extend State = iif(State in (Top_5_States), State, "Other")
| summarize sum(lightning_events) by State
状态State | sum_lightning_eventssum_lightning_events |
---|---|
ALABAMAALABAMA | 2929 |
威斯康星州WISCONSIN | 3131 |
德克萨斯TEXAS | 5555 |
佛罗里达州FLORIDA | 8585 |
佐治亚州GEORGIA | 106106 |
其他Other | 415415 |
使用函数返回的静态列表Use a static list returned by a function
StormEvents | where State in (InterestingStates()) | count
计数Count |
---|
47754775 |
函数定义。The function definition.
.show function InterestingStates
名称Name | parametersParameters | 正文Body | 文件夹Folder | DocStringDocString |
---|---|---|---|---|
InterestingStatesInterestingStates | ()() | { dynamic(["WASHINGTON", "FLORIDA", "GEORGIA", "NEW YORK"]) }{ dynamic(["WASHINGTON", "FLORIDA", "GEORGIA", "NEW YORK"]) } |