Oracle 聚合函数

最新推荐文章于 2024-05-27 01:18:31 发布

陈涛龙

最新推荐文章于 2024-05-27 01:18:31 发布

阅读量919

点赞数

分类专栏： Oracle 文章标签： oracle 数据库 database

本文链接：https://blog.csdn.net/ctlongs/article/details/122172170

版权

SELECT c.cust_id, ANY_VALUE(cust_last_name), SUM(amount_sold)
  FROM customers c, sales s
  WHERE s.cust_id = c.cust_id
  GROUP BY c.cust_id;

CUST_ID  ANY_VALUE(CUST_LAST_NAME) SUM(AMOUNT_SOLD)
------- -------------------------- ----------------
   6950 Sandburg                                 78
  17920 Oliver                                 3201
  66800 Case                                   2024
  37280 Edwards                                2256
 109850 Lindegreen                              757
   3910 Oddell                                  185
  84700 Marker                                164.4
  26380 Remler                                  118
  11600 Oppy                                    158
  23030 Rothrock                                533
  42780 Zanis                                   182
...
630 rows selected.

SELECT department_id, job_id, 
       APPROX_COUNT(*) 
FROM   employees
GROUP BY department_id, job_id
HAVING 
  APPROX_RANK ( 
  PARTITION BY department_id 
  ORDER BY APPROX_COUNT(*) 
  DESC ) <= 10;

SELECT APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers"
  FROM employees;

Active Managers
---------------
             18

SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"
  FROM sales
  GROUP BY prod_id
  ORDER BY prod_id;

   PROD_ID Number of Customers
---------- -------------------
        13                2516
        14                2030
        15                2105
        16                2367
        17                2093
        18                2975
        19                2630
        20                3791
. . .

CREATE MATERIALIZED VIEW daily_prod_count_mv AS
  SELECT t.calendar_year year,
         t.calendar_month_number month,
         t.day_number_in_month day,
         APPROX_COUNT_DISTINCT_DETAIL(s.prod_id) daily_detail
  FROM times t, sales s
  WHERE t.time_id = s.time_id
  GROUP BY t.calendar_year, t.calendar_month_number, t.day_number_in_month;

CREATE MATERIALIZED VIEW monthly_prod_count_mv AS
  SELECT year,
         month,
         APPROX_COUNT_DISTINCT_AGG(daily_detail) monthly_detail
  FROM daily_prod_count_mv
  GROUP BY year, month;

CREATE MATERIALIZED VIEW annual_prod_count_mv AS
  SELECT year,
         APPROX_COUNT_DISTINCT_AGG(daily_detail) annual_detail
  FROM daily_prod_count_mv
  GROUP BY year;

SELECT year,
       month,
       day,
       TO_APPROX_COUNT_DISTINCT(daily_detail) "NUM PRODUCTS"
  FROM daily_prod_count_mv
  ORDER BY year, month, day;

      YEAR      MONTH        DAY NUM PRODUCTS
---------- ---------- ---------- ------------
      1998          1

最低0.47元/天解锁文章

陈涛龙

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle 聚合函数

ANY_VALUE APPROX_COUNT APPROX_COUNT_DISTINCT APPROX_COUNT_DISTINCT_AGG APPROX_COUNT_DISTINCT_DETAIL APPROX_MEDIAN APPROX_PERCENTILE APPROX_PERCENTILE_AGG APPROX_PERCENTILE_DETAIL APPROX_RANK APPROX_SUM AVG BIT_AND_AGG BIT_OR_AGG BIT_XOR_AGG
复制链接

扫一扫

专栏目录