measure 聚合函数

适用于：检查标记为“是”的 Databricks SQL 检查标记为“是”的 Databricks Runtime 16.4 及更高版本

返回从组值中汇总的measure_column。 在 Databricks Runtime 18.1 及更高版本中， agg 聚合函数 是此函数的同义词。

与常规聚合函数（例如 SUM， AVG或 COUNT）不同，该 MEASURE 函数不指定聚合。 它从 指标视图定义继承聚合的定义。

将指标视图与度量值一起使用优于常规视图，因为它抽象化了基础聚合的复杂性，同时为调用方提供了选择分组列的自由。

语法

measure ( measure_column ) [ FILTER ( WHERE cond ) ]

不能使用子句将此OVER调用。

论据

  • measure_column：对指标视图中度量值列的引用。

  • cond： FILTER 子句 中的可选布尔表达式，用于筛选用于计算度量值的行。

    适用于：检查标记为“是”的 Databricks SQL 检查标记为“是”的 Databricks Runtime 18.1 及更高版本

退货

某种类型的值 measure_column。

FILTER 子句行为

向度量值应用 FILTER 子句时，筛选器条件将应用于度量值定义中的每个聚合函数：

  • 如果定义中的聚合函数没有 FILTER 子句，则度量值的条件将应用于它。
  • 如果定义中的聚合函数已有一个 FILTER 子句，则度量值的条件将与现有函数 AND结合使用。

当度量值引用另一个度量值时，相同的规则会递归应用。

对于窗口度量值，该 FILTER 子句在窗口聚合 后 应用，相当于在查询的 WHERE 子句中放置相同的条件。

在低于 18.1 的 Databricks Runtime 版本中，度量值上的子 FILTER 句返回错误。

例子

-- A metric view with a measure column 4 metric columns
CREATE OR REPLACE VIEW region_sales_metrics
  (month COMMENT 'Month order was made',
   status,
   order_priority,
   count_orders COMMENT 'Count of orders',
   total_Revenue,
   total_Revenue_p_Customer,
   total_revenue_for_open_orders)
  WITH METRICS
  LANGUAGE YAML
  COMMENT 'A metric view for regional sales metrics.'
  AS $$
   version: 0.1
   source: samples.tpch.orders
   filter: o_orderdate > '1990-01-01'
   dimensions:
   - name: month
     expr: date_trunc('MONTH', o_orderdate)
   - name: status
     expr: case
       when o_orderstatus = 'O' then 'Open'
       when o_orderstatus = 'P' then 'Processing'
       when o_orderstatus = 'F' then 'Fulfilled'
       end
   - name: order_priority
     expr: split(o_orderpriority, '-')[1]
   measures:
   - name: count_orders
     expr: count(1)
   - name: total_revenue
     expr: SUM(o_totalprice)
   - name: total_revenue_per_customer
     expr: SUM(o_totalprice) / count(distinct o_custkey)
   - name: total_revenue_for_open_orders
     expr: SUM(o_totalprice) filter (where o_orderstatus='O')
  $$;

-- Tracking total_revenue_per_customer by month in 1995
> SELECT extract(month from month) as month,
    measure(total_revenue_per_customer)::bigint AS total_revenue_per_customer
  FROM region_sales_metrics
  WHERE extract(year FROM month) = 1995
  GROUP BY ALL
  ORDER BY ALL;
  month	 total_revenue_per_customer
  ----- --------------------------
   1     167727
   2     166237
   3     167349
   4     167604
   5     166483
   6     167402
   7     167272
   8     167435
   9     166633
  10     167441
  11     167286
  12     167542

-- Tracking total_revenue_per_customer by month and status in 1995
> SELECT extract(month from month) as month,
    status,
    measure(total_revenue_per_customer)::bigint AS total_revenue_per_customer
  FROM region_sales_metrics
  WHERE extract(year FROM month) = 1995
  GROUP BY ALL
  ORDER BY ALL;
  month  status      total_revenue_per_customer
  ----- ---------   --------------------------
   1     Fulfilled   167727
   2     Fulfilled   161720
   2    Open          40203
   2    Processing   193412
   3    Fulfilled    121816
   3    Open          52424
   3    Processing   196304
   4    Fulfilled     80405
   4    Open          75630
   4    Processing   196136
   5    Fulfilled     53460
   5    Open         115344
   5    Processing   196147
   6    Fulfilled     42479
   6    Open         160390
   6    Processing   193461
   7    Open         167272
   8    Open         167435
   9    Open         166633
   10   Open         167441
   11   Open         167286
   12   Open         167542

-- Compare total revenue to revenue from fulfilled orders by month in 1995.
-- The FILTER condition is pushed down to the SUM aggregate in the total_revenue definition.
> SELECT extract(month from month) as month,
    measure(total_revenue)::bigint AS total_revenue,
    measure(total_revenue) FILTER (WHERE status = 'Fulfilled') AS fulfilled_revenue
  FROM region_sales_metrics
  WHERE extract(year FROM month) = 1995
  GROUP BY ALL
  ORDER BY ALL;
  • Last updated on