適用於：
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 執行環境 18.1 及以上版本
退貨
某種類別的值 measure_column。
FILTER 子句行為
當你對測度套用子 FILTER 句時，濾波條件會套用到該測度定義中的每個聚合函數：
- 若定義中的總體函數沒有
FILTER子句，則套用測度的條件。 - 若定義中的聚合函數已有
FILTER子句，則該測度條件與現有條件結合，使用AND。
當測度引用另一個測度時，同樣的規則也會遞迴適用。
對於視窗度量，子 FILTER 句會在視窗彙整 後 套用，等同於在查詢 WHERE 子句中放置相同條件。
在 18.1 以下的 Databricks 執行時版本中， 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;