当前位置： 首页 > 编程日记 > 正文

Oracle SQL高级编程——分析函数（窗口函数）全面讲解

编程日记 2025-01-04 11:10:00




Oracle SQL高级编程——分析函数（窗口函数）全面讲解

注：本文来源于：《Oracle SQL高级编程——分析函数（窗口函数）全面讲解》



概述

分析函数是以一定的方法在一个与当前行相关的结果子集中进行计算，也称为窗口函数。
一般结构为

Function(arg1 , arg2 ……) over(partition by clause order by clause windowing clause )Windowing clause : rows | range between start_expr and end_expr
Start_expr is unbounded preceding | current row | n preceding | n following
End_expr is unbounded following | current row | n preceding | n following


不是所有的分析函数都支持开窗子句。


创建测试表


create table sales_fact  asselect country_name country , country_subregion region , prod_name product , calendar_year year , calendar_week_number week ,sum(amount_sold) sale , sum(amount_sold*(case when mod(rownum , 10 ) = 0 then 1.4when mod(rownum , 5)= 0 then 0.6when mod(rownum , 2)= 0 then 0.9when mod(rownum , 2)=1 then 1.2else 1 end ) ) receiptsfrom sales , times , customers , countries , productswhere sales.time_id = times.time_id andsales.prod_id = products.prod_id andsales.cust_id = customers.cust_id andcustomers.country_id = countries.country_idgroup by country_name , country_subregion , prod_name , calendar_year , calendar_week_number ;


把聚合函数当作分析函数使用

分析函数列只是一列数值，每一行对应一个值，对于查询的其它方面没有任何影响。

从以下查询可以得出以下几点：


1.over分区条件中的列可以不在select列表中，但是必须在数据源中。
2.over排序条件中的列可以不在select列表中，但是必须在数据源中。
3.over排序条件是对所在分区中的数据进行排序，与select语句中的排序无关。但是会影响到分析函数的结果。
4.over中的开窗条件的范围一般仅限于分区本身。rows between unbounded preceding and current row表示从分区的最开始到当前行。
5.分析函数的数据来自结果集（施加了where条件之后的）。

下面的查询中的分析列表示该年从开始到该周的销售累计。

SH@ prod> select year , week , sale ,sum(sale) over( partition by region , year order by week  ) running_sum_ytdfrom sales_factwhere country in ('Australia') and product='Xtend Memory' and week < 10order by year , week ;YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------1998          1      58.15           58.151998          2      29.39           87.541998          3      29.49          117.031998          4      29.49          146.521998          5       29.8          176.321998          6      58.78           235.11998          9      58.78          293.881999          1      53.52           53.521999          3       94.6          148.121999          4       40.5          188.621999          5      80.01          268.631999          6       40.5          309.131999          8     103.11          412.241999          9      53.34          465.582000          1       46.7            46.72000          3      93.41          140.112000          4      46.54          186.652000          5       46.7          233.352000          7       70.8          304.152000          8      46.54          350.692001          1      92.26           92.262001          2     118.38          210.642001          3      47.24          257.882001          4      256.7          514.582001          5      93.44          608.022001          6      22.44          630.462001          7      69.96          700.42YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------2001          8      46.06          746.482001          9      92.67          839.1529 rows selected.

结果与上面相同，只是排序不同方式，分析列看起来就没有规律了。

SH@ prod> select year , week , sale , sum(sale) over( partition by region , year order by week  ) running_sum_ytd
  from sales_fact   where country in ('Australia') and product='Xtend Memory' and week < 10  order by year , sale ;YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------1998          2      29.39           87.541998          4      29.49          146.521998          3      29.49          117.031998          5       29.8          176.321998          1      58.15           58.151998          6      58.78           235.11998          9      58.78          293.881999          4       40.5          188.621999          6       40.5          309.131999          9      53.34          465.581999          1      53.52           53.521999          5      80.01          268.631999          3       94.6          148.121999          8     103.11          412.242000          4      46.54          186.652000          8      46.54          350.692000          1       46.7            46.72000          5       46.7          233.352000          7       70.8          304.152000          3      93.41          140.112001          6      22.44          630.462001          8      46.06          746.482001          3      47.24          257.882001          7      69.96          700.422001          1      92.26           92.262001          9      92.67          839.152001          5      93.44          608.02YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------2001          2     118.38          210.642001          4      256.7          514.5829 rows selected.


分区中的排序选取不恰当，则分析列结果没有什么意义了。分区开窗排序的选取与分析列的结果密切相关。

SH@ prod> select year , week , sale ,   sum(sale) over( partition by  region , year   order by sale ) running_sum_ytdfrom sales_factwhere country in ('Australia') and product='Xtend Memory' and week < 10  order by  year , week ;YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------1998          1      58.15          176.321998          2      29.39           29.391998          3      29.49           88.371998          4      29.49           58.881998          5       29.8          118.171998          6      58.78           235.11998          9      58.78          293.881999          1      53.52          187.861999          3       94.6          362.471999          4       40.5            40.51999          5      80.01          267.871999          6       40.5              811999          8     103.11          465.581999          9      53.34          134.342000          1       46.7          186.482000          3      93.41          350.692000          4      46.54           46.542000          5       46.7          139.782000          7       70.8          257.282000          8      46.54           93.082001          1      92.26          277.962001          2     118.38          582.452001          3      47.24          115.742001          4      256.7          839.152001          5      93.44          464.072001          6      22.44           22.442001          7      69.96           185.7YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------2001          8      46.06            68.52001          9      92.67          370.6329 rows selected.





分析函数的执行计划

虽然有分析函数还是只需要一次全表扫描，但是需要排序。

WINDOW SORT是分析函数的典型特征。

SH@ prod> explain plan for  select year , week , sale ,   sum(sale) over( partition by  region , year   order by sale   ) running_sum_ytd  
 from sales_fact   where country in ('Australia') and product='Xtend Memory' and week < 10   order by  year , week ;Explained.SH@ prod> select * from table(dbms_xplan.display()) ;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 173857439----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    18 |  1890 |   311   (1)| 00:00:04 |
|   1 |  SORT ORDER BY      |            |    18 |  1890 |   311   (1)| 00:00:04 |
|   2 |   WINDOW SORT       |            |    18 |  1890 |   311   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL| SALES_FACT |    18 |  1890 |   309   (1)| 00:00:04 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory' AND"WEEK"<10)Note
------ dynamic sampling used for this statement (level=2)   说明该表还没有统计信息。20 rows selected.

不加分析列，只是少了一步window sort。

SH@ prod> explain plan for2  select year , week , sale3  from sales_fact4  where country in ('Australia') and product='Xtend Memory' and week < 105  order by  year , week ;Explained.SH@ prod> select * from table(dbms_xplan.display()) ;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1978576542---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    18 |  1584 |   310   (1)| 00:00:04 |
|   1 |  SORT ORDER BY     |            |    18 |  1584 |   310   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| SALES_FACT |    18 |  1584 |   309   (1)| 00:00:04 |
---------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory' AND"WEEK"<10)Note
------ dynamic sampling used for this statement (level=2)19 rows selected.









如何使窗口充满整个分区

SH@ prod> select year , week , sale , max(sale) over(partition by product , country , region , year2  order by week3  rows between unbounded preceding and unbounded following )4  max_sale5  from sales_fact6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 107  order by product , country , year , week ;YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------1998          1      58.15      58.781998          2      29.39      58.781998          3      29.49      58.781998          4      29.49      58.781998          5       29.8      58.781998          6      58.78      58.781998          9      58.78      58.781999          1      53.52     103.111999          3       94.6     103.111999          4       40.5     103.111999          5      80.01     103.111999          6       40.5     103.111999          8     103.11     103.111999          9      53.34     103.112000          1       46.7      93.412000          3      93.41      93.412000          4      46.54      93.412000          5       46.7      93.412000          7       70.8      93.412000          8      46.54      93.412001          1      92.26      256.72001          2     118.38      256.72001          3      47.24      256.72001          4      256.7      256.72001          5      93.44      256.72001          6      22.44      256.72001          7      69.96      256.7YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------2001          8      46.06      256.72001          9      92.67      256.729 rows selected.


两个边界都滑动的窗口


下面语句的窗口是往前两周，加往后两周，加当前周，一共五周。（到达边界时窗口会自动缩小）

SH@ prod> select year , week , sale , max(sale) over(partition by product , country , region , year2  order by week3  rows between 2 preceding and 2 following )4  max_sale5  from sales_fact6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 107  order by product , country , year , week ;YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------1998          1      58.15      58.151998          2      29.39      58.151998          3      29.49      58.151998          4      29.49      58.781998          5       29.8      58.781998          6      58.78      58.781998          9      58.78      58.781999          1      53.52       94.61999          3       94.6       94.61999          4       40.5       94.61999          5      80.01     103.111999          6       40.5     103.111999          8     103.11     103.111999          9      53.34     103.112000          1       46.7      93.412000          3      93.41      93.412000          4      46.54      93.412000          5       46.7      93.412000          7       70.8       70.82000          8      46.54       70.8  这里只所以是70.8因为窗口缩小了。2001          1      92.26     118.382001          2     118.38      256.72001          3      47.24      256.72001          4      256.7      256.72001          5      93.44      256.72001          6      22.44      256.72001          7      69.96      93.44YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------2001          8      46.06      92.672001          9      92.67      92.6729 rows selected.





默认窗口是什么？

一看便知。

SH@ prod> select year , week , sale , max(sale) over(partition by product , country , region , year2  order by week )3  max_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , week ;YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------1998          1      58.15      58.151998          2      29.39      58.151998          3      29.49      58.151998          4      29.49      58.151998          5       29.8      58.151998          6      58.78      58.781998          9      58.78      58.781999          1      53.52      53.521999          3       94.6       94.61999          4       40.5       94.61999          5      80.01       94.61999          6       40.5       94.61999          8     103.11     103.111999          9      53.34     103.112000          1       46.7       46.72000          3      93.41      93.412000          4      46.54      93.412000          5       46.7      93.412000          7       70.8      93.412000          8      46.54      93.412001          1      92.26      92.262001          2     118.38     118.382001          3      47.24     118.382001          4      256.7      256.72001          5      93.44      256.72001          6      22.44      256.72001          7      69.96      256.7YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------2001          8      46.06      256.72001          9      92.67      256.729 rows selected.


Lead和Lag（不支持开窗的函数）

有开窗语句时会报这样的错

2
3
4rows between 2 preceding and 2 following )
*
ERROR at line 3:
ORA-00907: missing right parenthesis


LEAD是求下一个，而不是前一个。在分区的下边界处，LEAD处回空值。


SH@ prod> select year , week , sale , lead(sale) over(partition by product , country , region , year2  order by week  )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.15       29.391998          2      29.39       29.491998          3      29.49       29.491998          4      29.49        29.81998          5       29.8       58.781998          6      58.78       58.781998          9      58.781999          1      53.52        94.61999          3       94.6        40.51999          4       40.5       80.011999          5      80.01        40.51999          6       40.5      103.111999          8     103.11       53.341999          9      53.342000          1       46.7       93.412000          3      93.41       46.542000          4      46.54        46.72000          5       46.7        70.82000          7       70.8       46.542000          8      46.542001          1      92.26      118.382001          2     118.38       47.242001          3      47.24       256.72001          4      256.7       93.442001          5      93.44       22.442001          6      22.44       69.962001          7      69.96       46.06YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06       92.672001          9      92.6729 rows selected.


LAG求上一个，也就是前一个。在分区的上边界处返回空值。


SH@ prod> select year , week , sale , lag(sale) over(partition by product , country , region , year2  order by week  )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.151998          2      29.39       58.151998          3      29.49       29.391998          4      29.49       29.491998          5       29.8       29.491998          6      58.78        29.81998          9      58.78       58.781999          1      53.521999          3       94.6       53.521999          4       40.5        94.61999          5      80.01        40.51999          6       40.5       80.011999          8     103.11        40.51999          9      53.34      103.112000          1       46.72000          3      93.41        46.72000          4      46.54       93.412000          5       46.7       46.542000          7       70.8        46.72000          8      46.54        70.82001          1      92.262001          2     118.38       92.262001          3      47.24      118.382001          4      256.7       47.242001          5      93.44       256.72001          6      22.44       93.442001          7      69.96       22.44YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06       69.962001          9      92.67       46.0629 rows selected.


复杂的Lead和Lag

Lead和lag函数的第一参数为返回的列，第二参数为相隔行数（非负），第三个参数为不存在时的默认值（可以指定为当前行的值）。

SH@ prod> select year , week , sale , lag(sale , 2 , 0 ) over(partition by product , country , region , year2  order by week  )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.15           01998          2      29.39           01998          3      29.49       58.151998          4      29.49       29.391998          5       29.8       29.491998          6      58.78       29.491998          9      58.78        29.81999          1      53.52           01999          3       94.6           01999          4       40.5       53.521999          5      80.01        94.61999          6       40.5        40.51999          8     103.11       80.011999          9      53.34        40.52000          1       46.7           02000          3      93.41           02000          4      46.54        46.72000          5       46.7       93.412000          7       70.8       46.542000          8      46.54        46.72001          1      92.26           02001          2     118.38           02001          3      47.24       92.262001          4      256.7      118.382001          5      93.44       47.242001          6      22.44       256.72001          7      69.96       93.44YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06       22.442001          9      92.67       69.9629 rows selected.


将默认值指定为当前行的值。

SH@ prod> select year , week , sale , lag(sale , 2 , sale ) over(partition by product , country , region , year2  order by week  )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.15       58.151998          2      29.39       29.391998          3      29.49       58.151998          4      29.49       29.391998          5       29.8       29.491998          6      58.78       29.491998          9      58.78        29.81999          1      53.52       53.521999          3       94.6        94.61999          4       40.5       53.521999          5      80.01        94.61999          6       40.5        40.51999          8     103.11       80.011999          9      53.34        40.52000          1       46.7        46.72000          3      93.41       93.412000          4      46.54        46.72000          5       46.7       93.412000          7       70.8       46.542000          8      46.54        46.72001          1      92.26       92.262001          2     118.38      118.382001          3      47.24       92.262001          4      256.7      118.382001          5      93.44       47.242001          6      22.44       256.72001          7      69.96       93.44YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06       22.442001          9      92.67       69.9629 rows selected.


LEAD与LAG关于数据缺口的问题

LAG(sale , 10 ) 这表示与它相隔10行的数据，可是我想访问的10周前的数据。如果中间数据有缺口会出现严重的问题。


FIRST_VALUE和LAST_VALUE


这两个函数都可以与order by条件配合得到最大值和最小值。
First_value返回窗口中的第一个值。Ignore nulls表示忽略空值，如果第一个是空值返回第二个。

SH@ prod> select year , week , sale , first_value(sale ignore nulls) over(partition by product , country , region , year2  order by week3  rows between unbounded preceding and unbounded following )4  former_sale5  from sales_fact6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 107  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.15       58.151998          2      29.39       58.151998          3      29.49       58.151998          4      29.49       58.151998          5       29.8       58.151998          6      58.78       58.151998          9      58.78       58.151999          1      53.52       53.521999          3       94.6       53.521999          4       40.5       53.521999          5      80.01       53.521999          6       40.5       53.521999          8     103.11       53.521999          9      53.34       53.522000          1       46.7        46.72000          3      93.41        46.72000          4      46.54        46.72000          5       46.7        46.72000          7       70.8        46.72000          8      46.54        46.72001          1      92.26       92.262001          2     118.38       92.262001          3      47.24       92.262001          4      256.7       92.262001          5      93.44       92.262001          6      22.44       92.262001          7      69.96       92.26YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06       92.262001          9      92.67       92.2629 rows selected.


Last_value返回窗口中的最后一个值。Respect nulls表示识别空值，如果最后一个是空值也将其返回。



SH@ prod> select year , week , sale , last_value(sale respect nulls) over(partition by product , country , region , year2  order by week3  rows between unbounded preceding and unbounded following )4  former_sale5  from sales_fact6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 107  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.15       58.781998          2      29.39       58.781998          3      29.49       58.781998          4      29.49       58.781998          5       29.8       58.781998          6      58.78       58.781998          9      58.78       58.781999          1      53.52       53.341999          3       94.6       53.341999          4       40.5       53.341999          5      80.01       53.341999          6       40.5       53.341999          8     103.11       53.341999          9      53.34       53.342000          1       46.7       46.542000          3      93.41       46.542000          4      46.54       46.542000          5       46.7       46.542000          7       70.8       46.542000          8      46.54       46.542001          1      92.26       92.672001          2     118.38       92.672001          3      47.24       92.672001          4      256.7       92.672001          5      93.44       92.672001          6      22.44       92.672001          7      69.96       92.67YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06       92.672001          9      92.67       92.6729 rows selected.


NTH_VALUE访问分区别的任意指定行

FIRST_VALUE相当于NTH_VALUE（sale , 1 ）或者NTH_VALUE（sale , 1 ）from first respect nulls。
可以与排序配合求第几大，第几小。

SH@ prod> select year , week , sale , nth_value(sale , 1 ) from last ignore nulls over(partition by product , country , region , year2  order by week3  rows between unbounded preceding and unbounded following )4  former_sale5  from sales_fact6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 107  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.15       58.781998          2      29.39       58.781998          3      29.49       58.781998          4      29.49       58.781998          5       29.8       58.781998          6      58.78       58.781998          9      58.78       58.781999          1      53.52       53.341999          3       94.6       53.341999          4       40.5       53.341999          5      80.01       53.341999          6       40.5       53.341999          8     103.11       53.341999          9      53.34       53.342000          1       46.7       46.542000          3      93.41       46.542000          4      46.54       46.542000          5       46.7       46.542000          7       70.8       46.542000          8      46.54       46.542001          1      92.26       92.672001          2     118.38       92.672001          3      47.24       92.672001          4      256.7       92.672001          5      93.44       92.672001          6      22.44       92.672001          7      69.96       92.67YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06       92.672001          9      92.67       92.6729 rows selected.


RANK函数（不能开窗，作用于整个分区）


必须有排序条件，rank就是根据order by条件中的列来定排名的。
RANK函数的排名中，如果出现并列，排名将不连续。
如：1 2（2） 4 5 6 7 8 9 。 如果有两个第二名，那么第三名就不存在了。
请注意空值，在排序子句中可以使用NULLS LAST来把空值放在最后面

SH@ prod> select year , week , sale , rank() over(partition by product , country , region , year2  order by sale )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.15           5   没有31998          2      29.39           11998          3      29.49           21998          4      29.49           21998          5       29.8           41998          6      58.78           61998          9      58.78           61999          1      53.52           41999          3       94.6           61999          4       40.5           11999          5      80.01           51999          6       40.5           11999          8     103.11           71999          9      53.34           32000          1       46.7           32000          3      93.41           62000          4      46.54           12000          5       46.7           32000          7       70.8           52000          8      46.54           12001          1      92.26           52001          2     118.38           82001          3      47.24           32001          4      256.7           92001          5      93.44           72001          6      22.44           12001          7      69.96           4YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06           22001          9      92.67           629 rows selected.



DENSE_RANK（与RANK的区别在于排名一是连续的）


SH@ prod> select year , week , sale , dense_rank() over(partition by product , country , region , year2  order by sale )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , week ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          1      58.15           4  第三名是存在的1998          2      29.39           11998          3      29.49           21998          4      29.49           21998          5       29.8           31998          6      58.78           51998          9      58.78           51999          1      53.52           31999          3       94.6           51999          4       40.5           11999          5      80.01           41999          6       40.5           11999          8     103.11           61999          9      53.34           22000          1       46.7           22000          3      93.41           42000          4      46.54           12000          5       46.7           22000          7       70.8           32000          8      46.54           12001          1      92.26           52001          2     118.38           82001          3      47.24           32001          4      256.7           92001          5      93.44           72001          6      22.44           12001          7      69.96           4YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          8      46.06           22001          9      92.67           629 rows selected.


ROW_NUMBER（不支持开窗，不确定性函数）

为分区中的每一行指定一个递增的编号，如果排序的列的值相同，谁先谁后是随机的。

SH@ prod> select year , week , sale , row_number() over(partition by product , country , region , year2  order by sale )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , sale ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          2      29.39           11998          4      29.49           21998          3      29.49           31998          5       29.8           41998          1      58.15           51998          6      58.78           61998          9      58.78           71999          4       40.5           11999          6       40.5           21999          9      53.34           31999          1      53.52           41999          5      80.01           51999          3       94.6           61999          8     103.11           72000          4      46.54           12000          8      46.54           22000          5       46.7           32000          1       46.7           42000          7       70.8           52000          3      93.41           62001          6      22.44           12001          8      46.06           22001          3      47.24           32001          7      69.96           42001          1      92.26           52001          9      92.67           62001          5      93.44           7YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          2     118.38           82001          4      256.7           929 rows selected.


Ratio_to_report（当前行的值与分区总和的比值）

这个函数不支持排序和开窗。
求各周的销量在每年中的比例以及在整个产品销量中的比例。

SH@ prod> select year , week , sale ,2  trunc(100* ratio_to_report(sale) over(partition by year ) , 2) sales_yr ,3  trunc(100* ratio_to_report(sale) over() , 2 ) sales_prod4  from sales_fact5  where country in ('Australia') and product = 'Xtend Memory' and week < 106  order by year , week ;YEAR       WEEK       SALE   SALES_YR SALES_PROD
---------- ---------- ---------- ---------- ----------1998          1      58.15      19.78       2.981998          2      29.39         10        1.51998          3      29.49      10.03       1.511998          4      29.49      10.03       1.511998          5       29.8      10.14       1.521998          6      58.78         20       3.011998          9      58.78         20       3.011999          1      53.52      11.49       2.741999          3       94.6      20.31       4.851999          4       40.5       8.69       2.071999          5      80.01      17.18        4.11999          6       40.5       8.69       2.071999          8     103.11      22.14       5.281999          9      53.34      11.45       2.732000          1       46.7      13.31       2.392000          3      93.41      26.63       4.792000          4      46.54      13.27       2.382000          5       46.7      13.31       2.392000          7       70.8      20.18       3.632000          8      46.54      13.27       2.382001          1      92.26      10.99       4.732001          2     118.38       14.1       6.072001          3      47.24       5.62       2.422001          4      256.7      30.59      13.162001          5      93.44      11.13       4.792001          6      22.44       2.67       1.152001          7      69.96       8.33       3.58YEAR       WEEK       SALE   SALES_YR SALES_PROD
---------- ---------- ---------- ---------- ----------2001          8      46.06       5.48       2.362001          9      92.67      11.04       4.7529 rows selected.


Percent_rank（排在前百分之几）


用来求当前行的排名的相对百分位置。
比如你对人说自己是第10名，别人可能觉得没什么，如果是100000中的第10名，那就是前1/10000，那就非常牛了。
这个函数与RANK的推导公式为：
PERCENT_RANK = (RANK - 1) / (N – 1) , N代表总行数。
RANK – 1代表排名大于自己的人数。
N – 1代表除自己以外的总人数。
总体的意思是除自己之外的其它中人，排名比自己高的人所占的比例。


SH@ prod> select year , week , sale , rank() over(partition by product , country , region , year2  order by sale )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , sale ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          2      29.39           11998          4      29.49           21998          3      29.49           21998          5       29.8           41998          1      58.15           51998          6      58.78           61998          9      58.78           61999          4       40.5           11999          6       40.5           11999          9      53.34           31999          1      53.52           41999          5      80.01           51999          3       94.6           61999          8     103.11           72000          4      46.54           12000          8      46.54           12000          5       46.7           32000          1       46.7           32000          7       70.8           52000          3      93.41           62001          6      22.44           12001          8      46.06           22001          3      47.24           32001          7      69.96           42001          1      92.26           52001          9      92.67           62001          5      93.44           7YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          2     118.38           82001          4      256.7           929 rows selected.SH@ prod> select year , week , sale , 100*percent_rank() over(partition by product , country , region , year2  order by sale )3  former_sale4  from sales_fact5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 106  order by product , country , year , sale ;YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------1998          2      29.39           01998          4      29.49  16.66666671998          3      29.49  16.66666671998          5       29.8          501998          1      58.15  66.66666671998          6      58.78  83.33333331998          9      58.78  83.33333331999          4       40.5           01999          6       40.5           01999          9      53.34  33.33333331999          1      53.52          501999          5      80.01  66.66666671999          3       94.6  83.33333331999          8     103.11         1002000          4      46.54           02000          8      46.54           02000          5       46.7          402000          1       46.7          402000          7       70.8          802000          3      93.41         1002001          6      22.44           02001          8      46.06        12.52001          3      47.24          252001          7      69.96        37.52001          1      92.26          502001          9      92.67        62.52001          5      93.44          75YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------2001          2     118.38        87.52001          4      256.7         10029 rows selected.


Percentile_cont(大体意思求排在某个百分比时所需的数值)


也可以说是，现在说这样一个值，向分区里面插入这个值，其排名在百分之N（percent_rank为N%），求这个值。
如果有一个行的percent_rank正好等于N，那么就是这个么的值。如果没有匹配的，则要计算概率最大的。
SH@ prod> select year , week , sale ,2  percentile_cont(0.5) within group(order by sale desc )over(partition by year) pc ,3  percent_rank() over( partition by year order by sale desc ) pr4  from sales_fact5  where country in ('Australia') and product = 'Xtend Memory' and week < 11 ;YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------1998         10     117.76     43.975          01998          9      58.78     43.975 .1428571431998          6      58.78     43.975 .1428571431998          1      58.15     43.975 .4285714291998          5       29.8     43.975 .5714285711998          3      29.49     43.975 .7142857141998          4      29.49     43.975 .7142857141998          2      29.39     43.975          11999          8     103.11      62.76          01999          3       94.6      62.76 .1428571431999          5      80.01      62.76 .2857142861999         10         72      62.76 .4285714291999          1      53.52      62.76 .5714285711999          9      53.34      62.76 .7142857141999          6       40.5      62.76 .8571428571999          4       40.5      62.76 .8571428572000          3      93.41       46.7          02000          7       70.8       46.7         .22000          5       46.7       46.7         .42000          1       46.7       46.7         .42000          4      46.54       46.7         .82000          8      46.54       46.7         .82001          4      256.7      81.11          02001          2     118.38      81.11 .1111111112001          5      93.44      81.11 .2222222222001          9      92.67      81.11 .3333333332001          1      92.26      81.11 .444444444YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------2001          7      69.96      81.11 .5555555562001         10      69.05      81.11 .6666666672001          3      47.24      81.11 .7777777782001          8      46.06      81.11 .8888888892001          6      22.44      81.11          132 rows selected.



Percentile_disc（功能与Percentile_cont大体相同）


区别在于这个函数取到的值一定是在这个分区的行中的。
如果没有匹配的，Percentile_disc会按照排序取上一个。

SH@ prod> select year , week , sale ,2  percentile_disc(0.5) within group(order by sale desc )over(partition by year) pc ,3  percent_rank() over( partition by year order by sale desc ) pr4  from sales_fact5  where country in ('Australia') and product = 'Xtend Memory' and week < 11 ;YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------1998         10     117.76      58.15          01998          9      58.78      58.15 .1428571431998          6      58.78      58.15 .1428571431998          1      58.15      58.15 .4285714291998          5       29.8      58.15 .5714285711998          3      29.49      58.15 .7142857141998          4      29.49      58.15 .7142857141998          2      29.39      58.15          11999          8     103.11         72          01999          3       94.6         72 .1428571431999          5      80.01         72 .2857142861999         10         72         72 .4285714291999          1      53.52         72 .5714285711999          9      53.34         72 .7142857141999          6       40.5         72 .8571428571999          4       40.5         72 .8571428572000          3      93.41       46.7          02000          7       70.8       46.7         .22000          5       46.7       46.7         .42000          1       46.7       46.7         .42000          4      46.54       46.7         .82000          8      46.54       46.7         .82001          4      256.7      92.26          02001          2     118.38      92.26 .1111111112001          5      93.44      92.26 .2222222222001          9      92.67      92.26 .3333333332001          1      92.26      92.26 .444444444YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------2001          7      69.96      92.26 .5555555562001         10      69.05      92.26 .6666666672001          3      47.24      92.26 .7777777782001          8      46.06      92.26 .8888888892001          6      22.44      92.26          132 rows selected.SH@ prod> select year , week , sale ,2  percentile_cont(0.5) within group(order by sale desc )over(partition by year) pc ,3  percent_rank() over( partition by year order by sale desc ) pr4  from sales_fact5  where country in ('Australia') and product = 'Xtend Memory' and week < 11 ;YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------1998         10     117.76     43.975          01998          9      58.78     43.975 .1428571431998          6      58.78     43.975 .1428571431998          1      58.15     43.975 .4285714291998          5       29.8     43.975 .5714285711998          3      29.49     43.975 .7142857141998          4      29.49     43.975 .7142857141998          2      29.39     43.975          11999          8     103.11      62.76          01999          3       94.6      62.76 .1428571431999          5      80.01      62.76 .2857142861999         10         72      62.76 .4285714291999          1      53.52      62.76 .5714285711999          9      53.34      62.76 .7142857141999          6       40.5      62.76 .8571428571999          4       40.5      62.76 .8571428572000          3      93.41       46.7          02000          7       70.8       46.7         .22000          5       46.7       46.7         .42000          1       46.7       46.7         .42000          4      46.54       46.7         .82000          8      46.54       46.7         .82001          4      256.7      81.11          02001          2     118.38      81.11 .1111111112001          5      93.44      81.11 .2222222222001          9      92.67      81.11 .3333333332001          1      92.26      81.11 .444444444YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------2001          7      69.96      81.11 .5555555562001         10      69.05      81.11 .6666666672001          3      47.24      81.11 .7777777782001          8      46.06      81.11 .8888888892001          6      22.44      81.11          132 rows selected.


NTILE（类型于建立直方图，不支持开窗）


将排序后的数据均匀分配到指定个数据桶中，返回桶编号，如果不能等分，各个桶中的行数最多相差一行。
在以后的处理中可以通过去除首桶或尾去除异常值。
注意：并不是按值分配的。

SH@ prod> select year , week , sale ,2  ntile(10) over(order by sale ) group#3  from sales_fact4  where country in ('Australia') and product = 'Xtend Memory' and year = 1998 order by year , sale;YEAR       WEEK       SALE     GROUP#
---------- ---------- ---------- ----------1998         50      28.76          11998          2      29.39          11998          4      29.49          11998          3      29.49          11998          5       29.8          21998         43      57.52          21998         35      57.52          21998         40      57.52          21998         46      57.52          31998         27      57.52          31998         45      57.52          31998         44      57.52          31998         47      57.72          41998         29      57.72          41998         28      57.72          41998          1      58.15          41998         41      58.32          51998         51      58.32          51998         14      58.78          51998          9      58.78          51998         15      58.78          61998         17      58.78          61998          6      58.78          61998         19      58.98          61998         21       59.6          71998         12       59.6          71998         52      86.38          7YEAR       WEEK       SALE     GROUP#
---------- ---------- ---------- ----------1998         34     115.44          81998         39     115.84          81998         42     115.84          81998         38     115.84          91998         23     117.56          91998         18     117.56          91998         26     117.56         101998         10     117.76         101998         48     172.56         1036 rows selected.


Stddev计算标准差（方差的平方根，支持开窗）

SH@ prod> select year , week , sale ,2  stddev(sale) over(3  partition by product , country , region , year4  order by sale desc5  rows between 2 preceding and 2 following ) stddv6  from sales_fact7  where country in ('Australia') and product = 'Xtend Memory' and week < 108  order by year , week ;YEAR       WEEK       SALE      STDDV
---------- ---------- ---------- ----------1998          1      58.15 15.84534161998          2      29.39 .0577350271998          3      29.49 .1780215341998          4      29.49 12.79459181998          5       29.8  15.8157381998          6      58.78  .363730671998          9      58.78 14.38806541999          1      53.52  22.1789311999          3       94.6 21.73199021999          4       40.5 7.465500651999          5      80.01 22.97619921999          6       40.5 7.413177461999          8     103.11 11.68259531999          9      53.34 16.13055112000          1       46.7 21.00223322000          3      93.41 23.35896052000          4      46.54 .0923760432000          5       46.7 10.81392072000          7       70.8 22.42855382000          8      46.54 .0923760432001          1      92.26 20.38114522001          2     118.38 78.51522762001          3      47.24 26.50778982001          4      256.7  87.9471942001          5      93.44  71.3091932001          6      22.44 13.99009652001          7      69.96 22.9124643YEAR       WEEK       SALE      STDDV
---------- ---------- ---------- ----------2001          8      46.06  19.4076782001          9      92.67 17.140969129 rows selected.


Listagg(把分区中的列按照顺序拼接起来，不支持开窗)


SH@ prod> col stddv for a60
SH@ prod> select year , week , sale ,2  listagg(sale , ' , ')within group(order by sale desc) over(3  partition by product , country , region , year  ) stddv4  from sales_fact5  where country in ('Australia') and product = 'Xtend Memory' and week < 56  order by year , week ;YEAR       WEEK       SALE STDDV
---------- ---------- ---------- ------------------------------------------------------------1998          1      58.15 58.15 , 29.49 , 29.49 , 29.391998          2      29.39 58.15 , 29.49 , 29.49 , 29.391998          3      29.49 58.15 , 29.49 , 29.49 , 29.391998          4      29.49 58.15 , 29.49 , 29.49 , 29.391999          1      53.52 94.6 , 53.52 , 40.51999          3       94.6 94.6 , 53.52 , 40.51999          4       40.5 94.6 , 53.52 , 40.52000          1       46.7 93.41 , 46.7 , 46.542000          3      93.41 93.41 , 46.7 , 46.542000          4      46.54 93.41 , 46.7 , 46.542001          1      92.26 256.7 , 118.38 , 92.26 , 47.242001          2     118.38 256.7 , 118.38 , 92.26 , 47.242001          3      47.24 256.7 , 118.38 , 92.26 , 47.242001          4      256.7 256.7 , 118.38 , 92.26 , 47.2414 rows selected.






分析函数对谓词前推的影响

使用了分析函数的视图，会影响视图前推，因为分析函数的结果是跨行引用得来的，如果对数据源进行的剪裁，结果可能会不一样

SH@ prod> create or replace view max_5_weeks_vw as2  select country , product , region , year , week , sale ,3  max(sale) over(4  partition by product , country , region , year order by year , week5  rows between 2 preceding and 2 following ) max_weeks_56  from sales_fact ;View created.SH@ prod> select year , week , sale , max_weeks_5 from max_5_weeks_vw2  where country in ('Australia' ) and product = 'Xtend Memory'3  and region = 'Australia' and year = 2000 and week < 144  order by year , week ;YEAR       WEEK       SALE MAX_WEEKS_5
---------- ---------- ---------- -----------2000          1       46.7       93.412000          3      93.41       93.412000          4      46.54       93.412000          5       46.7       93.412000          7       70.8       93.742000          8      46.54       93.742000         11      93.74       117.52000         12      46.54      117.672000         13      117.5      117.679 rows selected.SH@ prod> explain plan for2  select year , week , sale , max_weeks_5 from max_5_weeks_vw3  where country in ('Australia' ) and product = 'Xtend Memory'4  and region = 'Australia' and year = 2000 and week < 145  order by year , week ;Explained.SH@ prod> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4167461139--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |    90 |  5220 |   310   (1)| 00:00:04 |
|*  1 |  VIEW               | MAX_5_WEEKS_VW |    90 |  5220 |   310   (1)| 00:00:04 |
|   2 |   WINDOW SORT       |                |    90 |  9450 |   310   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL| SALES_FACT     |    90 |  9450 |   309   (1)| 00:00:04 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("WEEK"<14)3 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory' AND"REGION"='Australia' AND "YEAR"=2000)Note
------ dynamic sampling used for this statement (level=2)21 rows selected.

对比没有分析函数的视图。直接将谓词推入到视图里面。

SH@ prod> create or replace view max_5_weeks_vw1 as2  select country , product , region , year , week , sale3  from sales_fact ;View created.SH@ prod> explain plan for2  select year , week , sale from max_5_weeks_vw13  where country in ('Australia' ) and product = 'Xtend Memory'4  and region = 'Australia' and year = 2000 and week < 145  order by year , week ;Explained.SH@ prod> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1978576542---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |   105 |   310   (1)| 00:00:04 |
|   1 |  SORT ORDER BY     |            |     1 |   105 |   310   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| SALES_FACT |     1 |   105 |   309   (1)| 00:00:04 |
---------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory' AND"REGION"='Australia' AND "YEAR"=2000 AND "WEEK"<14)Note
------ dynamic sampling used for this statement (level=2)19 rows selected.


分析函数用在动态SQL中

SH@ prod> create or replace procedure analytic_dynamic_prc ( part_col_string varchar2 , v_country varchar2 , v_product varchar2 )2  is3  type numtab is table of number(18 , 2) index by binary_integer ;4  l_year numtab ;5  l_week numtab ;6  l_sale numtab ;7  l_rank numtab ;8  l_sql_string varchar2(512) ;9  begin10  l_sql_string := 'select * from ( select year , week , sale , rank() over( partition by ' || part_col_string11  || ' order by sale desc ) sales_rank from sales_fact where country in ('12  || chr(39) || v_country || chr(39)13  || ' ) and product = ' || chr(39) || v_product || chr(39)14  || 'order by product , country , year , week ) where sales_rank <= 10  order by 1,4' ;15  execute immediate l_sql_string bulk collect into l_year , l_week , l_sale , l_rank ;16  for i in 1..l_year.count loop17  dbms_output.put_line( l_year(i) || ' | ' || l_week(i) || ' | ' || l_sale(i) || ' | ' || l_rank(i) ) ;18  end loop ;19  end ;20  /Procedure created.SH@ prod> exec analytic_dynamic_prc('product , country , region' , 'Australia' , 'Xtend Memory' ) ;
1998 | 48 | 172.56 | 9
2000 | 46 | 246.74 | 3
2000 | 21 | 187.48 | 5
2000 | 43 | 179.12 | 7
2000 | 34 | 178.52 | 8
2001 | 16 | 278.44 | 1
2001 | 4 | 256.7 | 2
2001 | 21 | 233.7 | 4
2001 | 48 | 182.96 | 6
2001 | 30 | 162.91 | 10
2001 | 14 | 162.91 | 10PL/SQL procedure successfully completed.


分析函数的“嵌套”

分析函数不能直接嵌套，可能通过子查询来实现。

select year , week , top_sale_year ,
lag( top_sale_year ) over ( order by year desc ) prev_top_sale_yer
from (select distinctfirst_value(year) over (   这里的作用不能用MAX代替，这里取列与排序的列是不同的。partition by product , country , region , yearorder by sale descrows between unbounded preceding and unbounded following ) year ,first_value(week) over (partition by product , country , region , yearorder by sale descrows between unbounded preceding and unbounded following ) week ,first_value(sale) over (partition by product , country , region , yearorder by sale descrows between unbounded preceding and unbounded following ) top_sale_yearfrom sales_factwhere country in ('Australia') and product = 'Xtend Memory' )
order by year , week ;


执行结果。

SH@ prod> select year , week , top_sale_year ,2  lag( top_sale_year ) over ( order by year desc ) prev_top_sale_yer3  from (4  select distinct5  first_value(year) over (6  partition by product , country , region , year7  order by sale desc8  rows between unbounded preceding and unbounded following ) year ,9  first_value(week) over (10  partition by product , country , region , year11  order by sale desc12  rows between unbounded preceding and unbounded following ) week ,13  first_value(sale) over (14  partition by product , country , region , year15  order by sale desc16  rows between unbounded preceding and unbounded following ) top_sale_year17  from sales_fact18  where country in ('Australia') and product = 'Xtend Memory' )19  order by year , week ;YEAR       WEEK TOP_SALE_YEAR PREV_TOP_SALE_YER
---------- ---------- ------------- -----------------1998         48        172.56            148.121999         17        148.12            246.742000         46        246.74            278.442001         16        278.44



分析函数的并行

SH@ prod> explain plan for2  select year , week , top_sale_year ,3  lag( top_sale_year ) over ( order by year desc ) prev_top_sale_yer4  from (5  select distinct6  first_value(year) over (7  partition by product , country , region , year8  order by sale desc9  rows between unbounded preceding and unbounded following ) year ,10  first_value(week) over (11  partition by product , country , region , year12  order by sale desc13  rows between unbounded preceding and unbounded following ) week ,14  first_value(sale) over (15  partition by product , country , region , year16  order by sale desc17  rows between unbounded preceding and unbounded following ) top_sale_year18  from sales_fact19  where country in ('Australia') and product = 'Xtend Memory' )20  order by year , week ;Explained.SH@ prod> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2124823565-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |   197 |  7683 |   313   (2)| 00:00:04 |
|   1 |  SORT ORDER BY         |            |   197 |  7683 |   313   (2)| 00:00:04 |
|   2 |   WINDOW SORT          |            |   197 |  7683 |   313   (2)| 00:00:04 |
|   3 |    VIEW                |            |   197 |  7683 |   311   (1)| 00:00:04 |
|   4 |     HASH UNIQUE        |            |   197 | 20685 |   311   (1)| 00:00:04 |
|   5 |      WINDOW SORT       |            |   197 | 20685 |   311   (1)| 00:00:04 |
|*  6 |       TABLE ACCESS FULL| SALES_FACT |   197 | 20685 |   309   (1)| 00:00:04 |
-------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------6 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory')Note
------ dynamic sampling used for this statement (level=2)22 rows selected.
（注意DISTINCT操作采用的是HASH UNIQUE而不是排序）



为上面的语句添加并行提示。

SH@ prod> explain plan for2  select /*+ parallel(3)*/ year , week , top_sale_year ,3  lag( top_sale_year ) over ( order by year desc ) prev_top_sale_yer4  from (5  select distinct6  first_value(year) over (7  partition by product , country , region , year8  order by sale desc9  rows between unbounded preceding and unbounded following ) year ,10  first_value(week) over (11  partition by product , country , region , year12  order by sale desc13  rows between unbounded preceding and unbounded following ) week ,14  first_value(sale) over (15  partition by product , country , region , year16  order by sale desc17  rows between unbounded preceding and unbounded following ) top_sale_year18  from sales_fact19  where country in ('Australia') and product = 'Xtend Memory' )20  order by year , week ;Explained.SH@ prod> set linesize 180
SH@ prod> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2880616722----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |   197 |  7683 |   119   (5)| 00:00:02 |        |      |            |
|   1 |  SORT ORDER BY                   |            |   197 |  7683 |   119   (5)| 00:00:02 |        |      |            |
|   2 |   WINDOW BUFFER                  |            |   197 |  7683 |   119   (5)| 00:00:02 |        |      |            |
|   3 |    PX COORDINATOR                |            |       |       |            |          |        |      |            |
|   4 |     PX SEND QC (ORDER)           | :TQ10003   |   197 |  7683 |   119   (5)| 00:00:02 |  Q1,03 | P->S | QC (ORDER) |
|   5 |      SORT ORDER BY               |            |   197 |  7683 |   119   (5)| 00:00:02 |  Q1,03 | PCWP |            |
|   6 |       PX RECEIVE                 |            |   197 |  7683 |   117   (3)| 00:00:02 |  Q1,03 | PCWP |            |
|   7 |        PX SEND RANGE             | :TQ10002   |   197 |  7683 |   117   (3)| 00:00:02 |  Q1,02 | P->P | RANGE      |
|   8 |         VIEW                     |            |   197 |  7683 |   117   (3)| 00:00:02 |  Q1,02 | PCWP |            |
|   9 |          HASH UNIQUE             |            |   197 | 20685 |   117   (3)| 00:00:02 |  Q1,02 | PCWP |            |
|  10 |           PX RECEIVE             |            |   197 | 20685 |   117   (3)| 00:00:02 |  Q1,02 | PCWP |            |
|  11 |            PX SEND HASH          | :TQ10001   |   197 | 20685 |   117   (3)| 00:00:02 |  Q1,01 | P->P | HASH       |
|  12 |             WINDOW SORT          |            |   197 | 20685 |   117   (3)| 00:00:02 |  Q1,01 | PCWP |            |
|  13 |              PX RECEIVE          |            |   197 | 20685 |   114   (0)| 00:00:02 |  Q1,01 | PCWP |            |
|  14 |               PX SEND HASH       | :TQ10000   |   197 | 20685 |   114   (0)| 00:00:02 |  Q1,00 | P->P | HASH       |
|  15 |                PX BLOCK ITERATOR |            |   197 | 20685 |   114   (0)| 00:00:02 |  Q1,00 | PCWC |            |
|* 16 |                 TABLE ACCESS FULL| SALES_FACT |   197 | 20685 |   114   (0)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------16 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory')Note
------ dynamic sampling used for this statement (level=2)- Degree of Parallelism is 3 because of hint33 rows selected.








Oracle 高级排序函数 和 高级分组函数

注：本内容来源于《Oracle 高级排序函数 和 高级分组函数》


高级排序函数:


[ ROW_NUMBER()| RANK() | DENSE_RANK ] OVER (partition by xx order by xx)
1.row_number() 连续且递增的数字 1 2 3 4
  row_number() over (partition by xx order by xx ) 
--学生表中按照所在专业分组，同专业内按成绩倒序排序，成绩相同则按学号正序排序，并给予组内等级
select row_number() over(partition by class_id order by score desc)rn,t.* from student2016 t
2.rank() 跳跃排序 若有相同数据则排名相同 然后跳跃排序 1 2 2 2 5
  rank() over (partition by xx order by xx )
select rank() over(partition by class_id order by score desc)rn,t.* from student2016 t
3.dense_rank 若有相同数据则排名相同 然后递增排序
dense_rank  over (partition by xx order by xx ) 1 2 2 2 3
select dense_rank() over(partition by class_id order by score desc)rn,t.* from student2016 t

----------------------------------------------------------------------------------------------------------------------------

高级分组函数

                  group by rollup(a,b,c)

select a，b,c,sum(d) from test group by rollup(a,b,c)

对rollup后面的列 按从右到左以少一列的方式进行分组直到所有列都去掉后的分组（也就是全表分组）
对于n个参数的 rollup，有n+1次分组

即按a，b，c，分组，union all a，b分组 union all a分组 union from test

----------------------------------------------------------------------------------

                  group by cube(a,b,c)

对n个参数，有2^n次分组

即按 ab，ac，a，bc，b，c最后对 全部分组

----------------------------------------------------------------------------------

                  group by grouping sets(a,b)

即只列出 对 a分组后，和对 b分组的结果集


-- 创建销售表
create table sales_tab(
year_id number not null,
month_id number not null,
day_id number not null,
sales_value number(10,2) not null
);-- 插入数据
insert into sales_tab
select trunc(dbms_random.value(low=>2010,high=>2012)) as year_id,
trunc(dbms_random.value(low=>1,high=>13)) as month_id,
trunc(dbms_random.value(low=>1,high=>32)) as day_id,
round(dbms_random.value(low=>1,high=>100)) as sales_value
from dual
connect by level <=1000;-- 查询 group by 后的数据
select sum(t.sales_value) from SALES_TAB t -- 1行select t.year_id,t.month_id,t.day_id,sum(t.sales_value) sales from SALES_TAB t group by t.year_id,t.month_id,t.day_id
order by t.year_id,t.month_id,t.day_id desc; -- 540行select t.year_id,t.month_id,sum(t.sales_value) sales from SALES_TAB t group by t.year_id,t.month_id
order by t.year_id,t.month_id desc; -- 24 行select t.year_id,sum(t.sales_value) sales from SALES_TAB t group by t.year_id
order by t.year_id desc; -- 2 行-- 使用高级分组函数
-- group by rollup（a,b,c）
select t.year_id,t.month_id,t.day_id,sum(t.sales_value) sales from SALES_TAB t group by rollup(t.year_id,t.month_id,t.day_id)
order by t.year_id,t.month_id,t.day_id; -- 567 行 = 同上面 1+540+24+2-- group by cube(a,b,c)
select t.year_id,t.month_id,t.day_id,sum(t.sales_value) sales from SALES_TAB t group by cube(t.year_id,t.month_id,t.day_id)
order by t.year_id,t.month_id,t.day_id;--group by grouping sets(a,b,c)
select t.year_id,t.month_id,sum(t.sales_value) sales from SALES_TAB t group by cube(t.year_id,t.month_id)
order by 1,2; -- 39 行select t.year_id,t.month_id,sum(t.sales_value) sales from SALES_TAB t group by grouping sets(t.year_id,t.month_id)
order by 1,2; -- 14 行



Oracle高级查询之OVER (PARTITION BY ..)

注：本内容来源于《Oracle高级查询之OVER (PARTITION BY ..)》

为了方便大家学习和测试，所有的例子都是在Oracle自带用户Scott下建立的。

注：标题中的红色order by是说明在使用该方法的时候必须要带上order by。

一、rank()/dense_rank() over(partition by ...order by ...)

现在客户有这样一个需求，查询每个部门工资最高的雇员的信息，相信有一定oracle应用知识的同学都能写出下面的SQL语句：

[sql] view plaincopy
  1. select e.ename, e.job, e.sal, e.deptno 
  2.   from scott.emp e, 
  3.        (select e.deptno, max(e.sal) sal from scott.emp e group by e.deptno) me 
  4.  where e.deptno = me.deptno 
  5.    and e.sal = me.sal; 

在满足客户需求的同时，大家应该习惯性的思考一下是否还有别的方法。这个是肯定的，就是使用本小节标题中rank() over(partition by...)或dense_rank() over(partition by...)语法，SQL分别如下：

[sql] view plaincopy
  1. select e.ename, e.job, e.sal, e.deptno 
  2.   from (select e.ename, 
  3.                e.job, 
  4.                e.sal, 
  5.                e.deptno, 
  6.                rank() over(partition by e.deptno order by e.sal desc) rank 
  7.           from scott.emp e) e 
  8.  where e.rank = 1; 
[sql] view plaincopy
  1. select e.ename, e.job, e.sal, e.deptno 
  2.   from (select e.ename, 
  3.                e.job, 
  4.                e.sal, 
  5.                e.deptno, 
  6.                dense_rank() over(partition by e.deptno order by e.sal desc) rank 
  7.           from scott.emp e) e 
  8.  where e.rank = 1; 

为什么会得出跟上面的语句一样的结果呢？这里补充讲解一下rank()/dense_rank() over(partition by e.deptno order by e.sal desc)语法。
over:  在什么条件之上。
partition by e.deptno:  按部门编号划分（分区）。
order by e.sal desc:  按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by否则非法）
rank()/dense_rank():  分级
整个语句的意思就是：在按部门划分的基础上，按工资从高到低对雇员进行分级，“级别”由从小到大的数字表示（最小值一定为1）。

那么rank()和dense_rank()有什么区别呢？
rank():  跳跃排序，如果有两个第一级时，接下来就是第三级。
dense_rank():  连续排序，如果有两个第一级时，接下来仍然是第二级。

小作业：查询部门最低工资的雇员信息。

二、min()/max() over(partition by ...)

现在我们已经查询得到了部门最高/最低工资，客户需求又来了，查询雇员信息的同时算出雇员工资与部门最高/最低工资的差额。这个还是比较简单，在第一节的groupby语句的基础上进行修改如下：

[sql] view plaincopy
  1. select e.ename, 
  2.          e.job, 
  3.          e.sal, 
  4.          e.deptno, 
  5.          e.sal - me.min_sal diff_min_sal, 
  6.          me.max_sal - e.sal diff_max_sal 
  7.     from scott.emp e, 
  8.          (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal 
  9.             from scott.emp e 
  10.            group by e.deptno) me 
  11.    where e.deptno = me.deptno 
  12.    order by e.deptno, e.sal; 

上面我们用到了min()和max()，前者求最小值，后者求最大值。如果这两个方法配合over(partition by ...)使用会是什么效果呢？大家看看下面的SQL语句：

[sql] view plaincopy
  1. select e.ename, 
  2.        e.job, 
  3.        e.sal, 
  4.        e.deptno, 
  5.        nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal, 
  6.        nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal 
  7.   from scott.emp e; 

这两个语句的查询结果是一样的，大家可以看到min()和max()实际上求的还是最小值和最大值，只不过是在partition by分区基础上的。

小作业：如果在本例中加上order by，会得到什么结果呢？

三、lead()/lag() over(partition by ... order by ...)

中国人爱攀比，好面子，闻名世界。客户更是好这一口，在和最高/最低工资比较完之后还觉得不过瘾，这次就提出了一个比较变态的需求，计算个人工资与比自己高一位/低一位工资的差额。这个需求确实让我很是为难，在groupby语句中不知道应该怎么去实现。不过。。。。现在我们有了over(partition by ...)，一切看起来是那么的简单。如下：

[sql] view plaincopy
  1. select e.ename, 
  2.        e.job, 
  3.        e.sal, 
  4.        e.deptno, 
  5.        lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal, 
  6.        lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal, 
  7.        nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal, 
  8.            0) diff_lead_sal, 
  9.        nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal 
  10.   from scott.emp e;  

看了上面的语句后，大家是否也会觉得虚惊一场呢（惊出一身冷汗后突然鸡冻起来，这样容易感冒）？我们还是来讲解一下上面用到的两个新方法吧。
lead(列名,n,m):  当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。
lag(列名,n,m):  当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

下面再列举一些常用的方法在该语法中的应用（注：带order by子句的方法说明在使用该方法的时候必须要带order by）：

[sql] view plaincopy
  1. select e.ename, 
  2.        e.job, 
  3.        e.sal, 
  4.        e.deptno, 
  5.        first_value(e.sal) over(partition by e.deptno) first_sal, 
  6.        last_value(e.sal) over(partition by e.deptno) last_sal, 
  7.        sum(e.sal) over(partition by e.deptno) sum_sal, 
  8.        avg(e.sal) over(partition by e.deptno) avg_sal, 
  9.        count(e.sal) over(partition by e.deptno) count_num, 
  10.        row_number() over(partition by e.deptno order by e.sal) row_num 
  11.   from scott.emp e; 


重要提示：大家在读完本片文章之后可能会有点误解，就是OVER (PARTITION BY ..)比GROUP BY更好，实际并非如此，前者不可能替代后者，而且在执行效率上前者也没有后者高，只是前者提供了更多的功能而已，所以希望大家在使用中要根据需求情况进行选择。

路在脚下






==================================================================================================================================================


https://www.dkcj.cn/info/34803.html

相关文章：

学习html5系列之比较典型的div滥用

在做网站过程过比较典型的div滥用&#xff0c;在很多网站中都可看到如下比较典型的div滥用情况。 div滥用情况&#xff1a; 网站首页新闻中心网站案例产品中心在线招聘联系我们优化后可实现相同效果&#xff1a; 网站首页新闻中心建站套餐产品中心关于我们联系我们 最上面的代码…

编程日记2025/01/04 11:00:00

【Qt】Qt再学习（八）：Media Player（Qt实现多媒体播放器）

1、简介 Media Player演示了一个简单的多媒体播放器，该播放器可以使用各种编解码器播放音频和/或视频文件。 涉及到的类有 QMediaPlayer、QMediaPlaylist、QVideoWidget、QVideoProbe、QAudioProbe 2、QMediaPlayer QMediaPlayer是一个媒体播放的高级类。它可以用来播放诸如…

编程日记2025/01/04 10:50:00

一次改变未来10年人生的机会

还记得陆奇在十问里说过马拉松和短跑的概念吗&#xff1f;你需要设计属于你自己的工作和生活节奏&#xff0c;一方面你可以保持高速&#xff0c;这个高速可以给你带来最大的效率。另一方面也需要可以应对突发变化&#xff0c;可以时不时的“冲刺”一下 &#xff08;比如偶尔过度…

编程日记2025/01/04 10:40:00

SQL Server 2008之WaitFor

在SQL Server 2005以上版本中&#xff0c;在一个增强的WaitFor命令&#xff0c;其作用可以和一个job相当。但使用更加简捷。 看MSDN:http://msdn.microsoft.com/zh-cn/library/ms187331.aspx 语法为&#xff1a;WAITFOR { DELAY time_to_pass | TIME time_to_execute | …

编程日记2025/01/04 10:30:00

“搞垮” 微博服务器？每天上亿条用户推送是如何做到的

记者 | 琥珀出品 | AI科技大本营&#xff08;ID&#xff1a;rgznai100&#xff09;想必国内绝大多数网民都有新浪微博的用户账号。据最新数据显示&#xff0c;2018 年第四季度财报&#xff0c;微博月活跃用户突破 4.62 亿&#xff0c;连续三年增长 7000 万 &#xff1b;微博垂直…

编程日记2025/01/04 10:20:00

【Qt】Qt再学习（九）：并发 QtConcurrent、QFuture、QFutureWatcher

1、QtConcurrent 该QtConcurrent命名空间提供高层次的API，使人们有可能不写使用低级线程原语的多线程程序，如互斥，读写锁，等待条件或信号。用QtConcurrent编写的程序会根据可用处理器内核的数量自动调整使用的线程数。这意味着，当将来在多核系统上部署时，今天编写的应用…

编程日记2025/01/04 10:10:00

Git——如何将本地项目提交至远程仓库(第一次)

1.&#xff08;先进入项目文件夹&#xff09;通过命令 git init 把这个目录变成git可以管理的仓库。 git init 2.把文件添加到版本库中&#xff0c;使用命令 git add .添加到暂存区里面去&#xff0c;不要忘记后面的小数点“.”&#xff0c;意为添加文件夹下的所有文件(夹)。 g…

编程日记2025/01/04 10:00:00

连接不上ftp解决方案

今天做linux下的ftp实验&#xff0c;总结一下解决连接不上ftp的解决方案&#xff1a;连接不上ftp解决方案&#xff1a;远程连接vsftp服务时&#xff0c;系统提示&#xff1a;用户没有权限访问。防火墙已经关闭&#xff0c;ftpusers和user_list文件已经删除了root用户。再使用命…

编程日记2025/01/04 09:50:00

Python3破冰人工智能，你需要掌握一些数学方法

为什么要把数学建模与当今火热的人工智能放在一起&#xff1f;首先&#xff0c;数学建模在字面上可以分解成数学建模&#xff0c;即运用统计学、线性代数和积分学等数学知识&#xff0c;构建算法模型&#xff0c;通过模型来解决问题。数学建模往往是没有对与错&#xff0c;只有…

编程日记2025/01/04 09:40:00

【Qt】QtCreator无法调试终端程序，启动报错SIGSTOP

1、问题描述 使用QtCreator调试终端程序时&#xff0c;因为收到信号SIGSTOP 而退出&#xff0c;无法调试程序。 2、解决方法 解决方式是&#xff0c;设置GDB不处理SIGSTOP &#xff0c; 在QtCreator中进入GDB命令设置窗口&#xff1a; Tools -> Options -> Debugger -…

编程日记2025/01/04 09:30:00

Centos7 下 配置 rsync 以及 rsync+inotify 实时同步

Centos 7 下 配置 Rsync 以及 rsyncinotify 实时同步 rsync介绍 rsync是一个开源的快速备份工具,可以在不同主机之间镜像同步整个目录树,支持增量备份,保持链接和权限,且采用优化的同步算法,在传输前执行压缩,因此非常适用于异地备份、镜像服务器等应用。 rsync的官方站点是htt…

编程日记2025/01/04 09:20:00

网站排名下降的原因

做站长&#xff0c;都经常遇到网站被降权、排名下降、百度快照后退等问题&#xff0c;这些也是企业网站最常见的一些问题&#xff0c;企业网站为何会被降权&#xff0c;通常是什么原因造成的&#xff0c;下面我在这里分享下我在北大青鸟学到的一些知识&#xff0c;简单的和大家…

编程日记2025/01/04 09:10:00

@程序员，Python 3还有哪些未Get的潜藏技能？| 技术头条

作者 | Vinko Kodžoman翻译 | Monanfei编辑 | 阿司匹林&#xff0c;Rachel【导读】在 Python 3 推出后&#xff0c;人们开始逐步将基于Python 2 的代码迁移至 Python 3 。但在迁移过程中&#xff0c;很多代码都未能使用到 Python 3 提供的新功能。本文作者介绍了相关功能的介绍…

编程日记2025/01/04 09:00:00

【Qt】QtCreator中配置clang-format

1、安装clang-format sudo apt install clang-format2、添加插件Beautifier 在QtCreator–>Help–>About Plugins…中添加插件Beautifer，添加后要重启QtCreator ClangCodeModel是否需要不清楚？反正我添加了 3、配置Clang Format 在Tools --> Options…–>Beau…

编程日记2025/01/04 08:50:00

DVWA提示Unable to connect to the database.

因为数据库更换了默认端口&#xff0c;所以得在DVWA也进行相应的设置&#xff0c;但是设置的位置错了&#xff0c;导致一直连接不上数据库。 后面看到注释才发现是这个设置仅限PostgreSQL/PGSQL使用&#xff0c;至于Mysql更换端口以后直接设置在server地址后面即可。 刷新一下&…

编程日记2025/01/04 08:40:00

LINQ to SQL语句之 Count/Sum/Min/Max/Avg

Count/Sum/Min/Max/Avg操作符 适用场景&#xff1a;统计数据吧&#xff0c;比如统计一些数据的个数&#xff0c;求和&#xff0c;最小值&#xff0c;最大值&#xff0c;平均数。 Count 说明&#xff1a;返回集合中的元素个数&#xff0c;返回INT类型&#xff1b;不延迟。生成SQ…

编程日记2025/01/04 08:30:00

【Qt】Qt再学习（十）：鼠标拖拽（dragdrop）QGraphicsItem示例

1、QGraphicsItem实现拖拽源 实现方法，继承QGraphicsItem，重载鼠标按下、移动、释放事件处理函数 class ColorItem : public QGraphicsItem {... protected:void mousePressEvent(QGraphicsSceneMouseEvent *event) override;void mouseMoveEvent(QGraphicsSceneMouseEvent…

编程日记2025/01/04 08:20:00

Java并发 -- JMM

文章基于jdk1.7&#xff0c;通过学习《Java并发编程的艺术》&#xff0c;对Java内存模型的理解 并发编程模型的两个关键问题 线程之间如何通信线程之间如何同步上面所说的线程指的是并发执行的活动实体。 线程之间的通信机制有两种&#xff1a;共享内存和消息传递 在共享内存的…

编程日记2025/01/04 08:10:00

开源！mathAI手写拍照自动能解高数题，还不快试试？

作者 | 红色石头转载自 AI有道&#xff08;id&#xff1a;redstonewill)让我们不妨先来盘点下从 2016 年起过去三年间 Google I/O 开发者大会亮相的重磅 AI 产品&#xff1a;深度好玩&#xff01;文章开始红色石头先在草稿纸上写一道高数微积分题目给大家看看如何求解&#xf…

编程日记2025/01/04 08:00:00

黄聪：IE6下用控制图片最大显示尺寸

div img { max-width:600px; width:600px; width:expression(document.body.clientWidth>600?"600px":"auto");overflow:hidden; } ◎ max-width:600px; 在IE7、FF等其他非IE浏览器下最大宽度为600px。但在IE6中无效。 ◎ width:600px; 在所有浏览器中…

编程日记2025/01/04 07:50:00

漫画：有趣的海盗问题 （完整版）

————— 第二天 —————海盗分金币问题&#xff1a;有5个海盗&#xff0c;获得了100枚金币&#xff0c;于是他们要商量一个方法来分配金币。商议方式如下&#xff1a;1. 由5个海盗轮流提出分配方案。2. 如果超过半数海盗&#xff08;包括提出者&#xff09;同意该方案&…

编程日记2025/01/04 07:40:00

【Qt】error: undefined reference to `vtable for MainWindow‘

1、问题描述 在写一个demo时，想尽量简单，就把MainWindow类的定义和实现都写在main.cpp中，结果编译时报错： main.cpp:-1: error: undefined reference to `vtable for MainWindow :-1: error: collect2.exe: error: ld returned 1 exit status2、原因分析 错误信息vtable…

编程日记2025/01/04 07:30:00

Sql Server：创建用户并指定该用户只能看指定的视图

1&#xff0c;在sql server中选择好要操作的数据库2,--当前数据库创建角色 exec sp_addrole seeview--创建了一个数据库角色&#xff0c;名称为:[seeview]3,--分配视图权限 GRANT SELECT ON veiw TO [角色] --指定视图列表 指定seeview这个角色可以查看的视图表名称&#xff1…

编程日记2025/01/04 07:20:00

10 款可以找回删除文件的好软件

电脑突然死机或者断电&#xff0c;硬盘数据丢失?U盘重要文件不小心删掉了? 电脑中毒,文件丢失或无法读取? 系统突然崩溃,重要文件丢失?使用计算机最怕的就是象以上这些突如其来的灾难性故障导致重要数据的丢失&#xff0c;误操作、计算机病毒的***和软、硬件故障等天灾人祸…

编程日记2025/01/04 07:10:00

万万没想到，逛B站也能学编程了

作者 | 徐麟&#xff0c;某互联网公司数据分析狮转载自 | 数据森麟&#xff08;id&#xff1a;shujusenlin&#xff09;前言很多人提到B站&#xff0c;首先想到的就会是二次元或者鬼畜&#xff0c;上个月&#xff0c;我们公众号也发表了一篇关于B站鬼畜视频的文章&#xff1a;《…

编程日记2025/01/04 07:00:00

【Qt】Qt再学习（十一）：图形视图框架（Graphics View Framework）的一个小demo

1、简介 使用的主要类：QGraphicsTextItem、QGraphicsScene、QGraphicsView。 QGraphicsTextItem继承自QGraphicsObject，而QGraphicsObject继承自QObject和QGraphicsItem； QGraphicsItem可以是2D曲面上的图形项目，例如线条，矩形，文本甚至自定义项目，本例中演示的文本QGr…

编程日记2025/01/04 06:50:00

国产海翼号水下滑翔机首次应用于北极科考

中国第九次北极科学考察队7月28日在白令海公海区域成功布放我国自主研发的“海翼”号水下滑翔机。这是我国自主研发的水下滑翔机首次在白令海布放&#xff0c;也是首次应用于中国北极科考。 北京时间28日5时23分&#xff0c;考察队队员们将“海翼”号水下滑翔机缓缓送入海中。在…

编程日记2025/01/04 06:40:00

高性能网站性能优化与系统架构(ZT)

转载请保留出处&#xff1a;俊麟 Michael’s blog (http://space.itpub.net/7311285/viewspace-97) 我在CERNET做过拨号接入平台的搭建&#xff0c;而后在Yahoo&3721从事过搜索引擎前端开发&#xff0c;又在MOP处理过大型社区猫扑大杂烩的架构升级等工作&#xff0c;同时自…

编程日记2025/01/04 06:30:00

非常全面的AutoML资源，看这个就够了！

整理 | Rachel责编 | 琥珀出品 | AI科技大本营&#xff08;id&#xff1a;rgznai100&#xff09;深度学习的发展促进了相关应用的涌现。但是&#xff0c;深度学习模型往往具有非常大的参数搜索空间&#xff0c;为了保证模型的效果&#xff0c;经常需要机器学习专家耗费大量的时…

编程日记2025/01/04 06:20:00

【Qt】Qt再学习（十二）：QGraphicsItem

1、简介 QGraphicsItem是用于场景QGraphicsScene中在所有图形项的基类，它是一个纯虚类，不能直接使用。子类化QGraphicsItem后，需要重新实现图形项的几何形状、碰撞检测、绘画实现、事件处理。 常用图形项： 类名图形项QGraphicsEllipseItem椭圆QGraphicsLineItem直线QGrap…

编程日记2025/01/04 06:10:00