다음을 통해 공유

measure 집계 함수

적용 대상:확인 표시 예 Databricks SQL 확인 표시 예 Databricks Runtime 16.4 이상

measure_column 그룹 값에서 집계된 값을 반환합니다.

일반 집계 함수 SUM, AVG, 또는 COUNT와 달리, MEASURE 함수는 집계를 지정하지 않습니다. 메트릭 뷰 정의에서 집계 정의를 상속합니다.

측정값과 함께 메트릭 뷰를 사용하는 것은 기본 집계의 복잡성을 추상화하면서 호출자에게 그룹화 열을 자유롭게 선택할 수 있도록 하기 때문에 일반 보기보다 우수합니다.

문법

measure ( measure_column )

이 함수는 절을 사용하여 OVER로는 호출할 수 없습니다.

주장들

  • measure_column: 메트릭 뷰의 측정값 열에 대한 참조입니다.

반품

measure_column 유형의 값입니다.

예시

-- 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