工作中遇到的SQL二（更新中）

最新推荐文章于 2024-01-29 11:52:35 发布
最新推荐文章于 2024-01-29 11:52:35 发布
阅读量1.3k 收藏
点赞数
分类专栏： Sql 文章标签： SQL 问题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/fuwencaho/article/details/24470417
版权
本文深入探讨了SQL窗口函数的概念、语法及其在不同场景下的应用，包括分区、框架字句的使用，以及如何利用窗口函数进行复杂的数据分析。通过实例演示，展示了窗口函数在计算累计和、移动平均等操作上的优势。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

--求两列的差值

求两个不同员工之间工资差

SELECT a-b FROM (select salary a FROM  STAFF WHERE ID=10 ) x ,(select salary b FROM  STAFF WHERE ID=20) y

--另

表1：
ID         VALUE
1          80
2          300
3          350
4          400

想得到的结果是:
ID         VALUE
1          220                      //300-80
2          50                       //350-50
3          50
--SQL 

select a.id ,(b.value-a.value) value from tables a,tables b 
where a.id=(b.id-1)

你放看看这种解法：

 select salary,abs((salary-(select salary from STAFF  where id>t.id order by id FETCH FIRST 1 ROWS ONLY )))
as result
from STAFF  t


高手如云呀：

with tb as
(
  select *, rownr = ROW_NUMBER()VOER(ORDER BY id)
  from table1
)
update table1
set result = y.id-x.id
from tb x
left outer join tb y on x.rownr +1 = y.rownr 


----over子句的使用


SELECT NAME, DEPT,
RANK () OVER (PARTITION BY DEPT ORDER BY SALARY DESC) AS RANK,
SUM (SALARY) OVER (PARTITION BY DEPT
ORDER BY SALARY DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS CUMULATIVE_SUM
FROM STAFF
WHERE DEPT IN (15,38)
ORDER BY DEPT, RANK

结果如下：

<img src="https://img-blog.youkuaiyun.com/20140425174654000?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvZnV3ZW5jYWhv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" alt="" />

一--
一开始看不懂，这是什么意思，虽然之前接触过一点over，但是这个还是有一点知识点不是很明白

比如：

SUM (SALARY) OVER (PARTITION BY DEPT
ORDER BY SALARY DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

后面ROWS...是神马

http://msdn.microsoft.com/zh-cn/library/ms189461.aspx

不贴出来了，具体看上面的网站

PARTITION BY

将查询结果集分为多个分区。 开窗函数分别应用于每个分区，并为每个分区重新启动计算。

value_expression

指定行集按其分区的列。 value_expression 只能引用可供 FROM 子句使用的列。 value_expression 不能引用选择列表中的表达式或别名。 value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量。

<ORDER BY 子句>

定义结果集的每个分区中行的逻辑顺序。 也就是说，它指定按其执行开窗函数计算的逻辑顺序。

order_by_expression

指定用于进行排序的列或表达式。 order_by_expression 只能引用可供 FROM 子句使用的列。 不能将整数指定为表示列名或别名。

COLLATE  collation_name

指定 ORDER BY 运算应该根据在 collation_name 中指定的排序规则执行。 collation_name 既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。 有关详细信息，请参阅排序规则和 Unicode 支持。 COLLATE 仅适用于 char、varchar、nchar 和 nvarchar 类型的列。

ASC | DESC

指定按升序或降序排列指定列中的值。 ASC 是默认排序顺序。 Null 值被视为最低的可能值。

ROWS | RANGE

通过指定分区中的起点和终点，进一步限制分区中的行数。 这是通过按照逻辑关联或物理关联对当前行指定某一范围的行实现的。 物理关联通过使用 ROWS 子句实现。

ROWS 子句通过指定当前行之前或之后的固定数目的行，限制分区中的行数。 此外，RANGE 子句通过指定针对当前行中的值的某一范围的值，从逻辑上限制分区中的行数。 基于 ORDER BY 子句中的顺序对之前和之后的行进行定义。 窗口框架“RANGE … CURRENT ROW …”包括在 ORDER BY 表达式中与当前行具有相同值的所有行。例如，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 意味着该函数对其操作的行的窗口在大小上是 3 行、以当前行之前（包括当前行）的 2 行开头。

注意 注意

ROWS 或 RANGE 要求指定 ORDER BY 子句。 如果 ORDER BY 包含多个顺序表达式，则 CURRENT ROW FOR RANGE 在确定当前行时将考虑 ORDER BY 列表中的所有列。

UNBOUNDED PRECEDING

指定窗口在分区中的第一行开始。 UNBOUNDED PRECEDING 只能指定为窗口起点。

<无符号值指定> PRECEDING

使用 <无符号值指定> 指示要置于当前行之前的行或值的数目。 对于 RANGE 则不允许这样指定。

CURRENT ROW

在与 ROWS 一起使用时指定窗口在当前行开始或结束，或者在与 RANGE 一起使用时指定当前值。 CURRENT ROW 可指定为既是起点，又是终点。

BETWEEN <窗口框架限定 > AND <窗口框架限定 >

与 ROWS 或 RANGE 一起使用，以便指定窗口的下（开始）边界和上（结束）边界点。 <窗口框架限定> 定义边界起点，<窗口框架限定> 定义边界终点。 上限不能小于下限。

UNBOUNDED FOLLOWING

指定窗口在分区的最后一行结束。 UNBOUNDED FOLLOWING 只能指定为窗口终点。 例如，RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 定义以当前行开始、以分区的最后一行结束的窗口。

<无符号值指定> FOLLOWING

使用 <无符号值指定> 指示要置于当前行之后的行或值的数目。 在 <无符号值指定> FOLLOWING 指定为窗口起点时，终点必须是 <无符号值指定>FOLLOWING。 例如，ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING 定义一个窗口，该窗口以跟随在当前行之后的第二行开头、以跟随在当前行之后的第十行结尾。 对于 RANGE 则不允许这样指定。

无符号整数文字

一个正整数文字（包括 0），它指定要置于当前行或值之前或之后的行或值的数目。 这一指定仅对于 ROWS 有效。



看到这是不是明白点什么，是不是累加的意思，是不是和最初的求差有点异曲同工之妙

看几个事例吧，加深印象：

A.将 OVER 子句与 ROW_NUMBER 函数结合使用

SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number", 
    p.LastName, s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson AS s 
    INNER JOIN Person.Person AS p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0
ORDER BY PostalCode

下面是结果集：
Row Number      LastName                SalesYTD              PostalCode
--------------- ----------------------- --------------------- ----------
1               Mitchell                4251368.5497          98027
2               Blythe                  3763178.1787          98027
3               Carson                  3189418.3662          98027
4               Reiter                  2315185.611           98027
5               Vargas                  1453719.4653          98027
6               Ansman-Wolfe            1352577.1325          98027
1               Pak                     4116871.2277          98055
2               Varkey Chudukatil       3121616.3202          98055
3               Saraiva                 2604540.7172          98055
4               Ito                     2458535.6169          98055
5               Valdez                  1827066.7118          98055
6               Mensa-Annan             1576562.1966          98055
7               Campbell                1573012.9383          98055
8               Tsoflias                1421810.9242          98055

B.将 OVER 子句与聚合函数结合使用

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

下面是结果集： 

SalesOrderID ProductID   OrderQty Total       Avg         Count       Min    Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659        776         1        26          2           12          1      6
43659        777         3        26          2           12          1      6
43659        778         1        26          2           12          1      6
43659        771         1        26          2           12          1      6
43659        772         1        26          2           12          1      6
43659        773         2        26          2           12          1      6
43659        774         1        26          2           12          1      6
43659        714         3        26          2           12          1      6
43659        716         1        26          2           12          1      6
43659        709         6        26          2           12          1      6
43659        712         2        26          2           12          1      6
43659        711         4        26          2           12          1      6
43664        772         1        14          1           8           1      4
43664        775         4        14          1           8           1      4
43664        714         1        14          1           8           1      4
43664        716         1        14          1           8           1      4
43664        777         2        14          1           8           1      4
43664        771         3        14          1           8           1      4
43664        773         1        14          1           8           1      4
43664        778         1        14          1           8           1      4

以下示例显示在计算所得值中将  OVER  子句与聚合函数结合使用。 

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
    ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID) 
        *100 AS DECIMAL(5,2))AS "Percent by ProductID"
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

下面是结果集：注意，聚合由  SalesOrderID  计算，并会为每个  SalesOrderID  的每一行计算  Percent by ProductID （ProductID 的百分比）。 

SalesOrderID ProductID   OrderQty Total       Percent by ProductID
------------ ----------- -------- ----------- ---------------------------------------
43659        776         1        26          3.85
43659        777         3        26          11.54
43659        778         1        26          3.85
43659        771         1        26          3.85
43659        772         1        26          3.85
43659        773         2        26          7.69
43659        774         1        26          3.85
43659        714         3        26          11.54
43659        716         1        26          3.85
43659        709         6        26          23.08
43659        712         2        26          7.69
43659        711         4        26          15.38
43664        772         1        14          7.14
43664        775         4        14          28.57
43664        714         1        14          7.14
43664        716         1        14          7.14
43664        777         2        14          14.29
43664        771         3        14          21.4
43664        773         1        14          7.14
43664        778         1        14          7.14

C.生成移动平均值和累计合计

下面的示例将 AVG 和 SUM 函数与 OVER 子句结合使用，以便为 Sales.SalesPerson 表中的每个地区提供年度销售额的累计合计。 数据按 TerritoryID 分区并在逻辑上按SalesYTD 排序。 这意味着，将基于年度销售额为各区域计算 AVG 函数。 请注意，对于 TerritoryID 1，为 2005 销售年度存在两行，分别表示在该年度有销售业绩的两个销售人员。 将计算这两行的平均销售额，然后在计算中包括表示 2006 年销售额的第三行。

SELECT BusinessEntityID, TerritoryID 
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID 
                                            ORDER BY DATEPART(yy,ModifiedDate) 
                                           ),1) AS MovingAvg
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 
                                            ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

-- 

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           559,697.56           559,697.56
287              NULL        2006        519,905.93           539,801.75           1,079,603.50
285              NULL        2007        172,524.45           417,375.98           1,252,127.95
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17

在这个例子中，OVER 子句未包含 PARTITION BY。   这意味着该函数将应用于查询所返回的所有行。   在 OVER 子句中指定的 ORDER BY 子句将确定应用 AVG 函数的逻辑顺序。   该查询将按年为在 WHERE 子句中指定的所有销售区域返回销售额的移动平均值。   在 SELECT 语句中指定的 ORDER BY 子句将确定显示查询行的顺序。 

SELECT BusinessEntityID, TerritoryID 
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS MovingAvg
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

-- 

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93

D.指定 ROWS 子句

下面的示例使用 ROWS 子句定义其行将作为当前行以及后随的 N 行（在此示例中为 1 行）计算的窗口。

SELECT BusinessEntityID, TerritoryID 
    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
    ,DATEPART(yy,ModifiedDate) AS SalesYear
    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 
                                             ORDER BY DATEPART(yy,ModifiedDate) 
                                             ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;

下面是结果集：

BusinessEntityID TerritoryID SalesYTD             SalesYear   CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274              NULL        559,697.56           2005        1,079,603.50
287              NULL        519,905.93           2006        692,430.38
285              NULL        172,524.45           2007        172,524.45
283              1           1,573,012.94         2005        2,925,590.07
280              1           1,352,577.13         2005        2,929,139.33
284              1           1,576,562.20         2006        1,576,562.20
275              2           3,763,178.18         2005        3,763,178.18
277              3           3,189,418.37         2005        3,189,418.37
276              4           4,251,368.55         2005        6,709,904.17
281              4           2,458,535.62         2005        2,458,535.62

在下面的示例中，使用 UNBOUNDED PRECEDING 指定 ROWS 子句。 结果为窗口在分区中的第一行开始。

SELECT BusinessEntityID, TerritoryID 
    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
    ,DATEPART(yy,ModifiedDate) AS SalesYear
    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 
                                             ORDER BY DATEPART(yy,ModifiedDate) 
                                             ROWS UNBOUNDED PRECEDING),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;

下面是结果集：

BusinessEntityID TerritoryID SalesYTD             SalesYear   CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274              NULL        559,697.56           2005        559,697.56
287              NULL        519,905.93           2006        1,079,603.50
285              NULL        172,524.45           2007        1,252,127.95
283              1           1,573,012.94         2005        1,573,012.94
280              1           1,352,577.13         2005        2,925,590.07
284              1           1,576,562.20         2006        4,502,152.27
275              2           3,763,178.18         2005        3,763,178.18
277              3           3,189,418.37         2005        3,189,418.37
276              4           4,251,368.55         2005        4,251,368.55
281              4           2,458,535.62         2005        6,709,904.17
上面的SQL可能针对于MS,但是相对于其他数据库来说可能略有差别，但是基本思想一致！！！！




在引用一篇吧，当个专题了

http://www.cnblogs.com/sirc/archive/2010/06/28/1766981.html

SQL中的窗口函数

与聚集函数一样，窗口函数也针对定义的行集（组）执行聚集，但它不像聚集函数那样每组之返回一个值，窗口函数可以为每组返回多个值。实际上，DB2中称这种函数为联机分析处理OLAP函数，而Oracle把它们称为解析函数，但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。

窗口函数：

聚合函数 over()

聚合函数 over(partition by 字段）—分区

聚合函数 over(order by 字段)--框架字句

 

本文以Oracle11g中HR模式下的Employees表为例子来试着了解窗口函数，

Employees表结构如下：

SQL> desc employees
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

 

计算部门号位20的员工总数：

SQL> edit
  1  select first_name,department_id,count(*) over()
  2  from employees
  3* where department_id=20
SQL> /

FIRST_NAME           DEPARTMENT_ID COUNT(*)OVER()                               
-------------------- ------------- --------------                               
Michael                         20              2                               
Pat                             20              2

 

窗口 ，函数 count(*) over() 对于查询返回的每一行，它返回了表中所有行的计数。

在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。

 

  1. 分区

使用Partiton by字句定义行的分区或组，可以用paritition by对定义的行组计算聚集（当遇到新组的时候复位），并返回每个值（每个组中的每个成员），而不是一个用一个组表示表中的这个值的所有实例。如：

SQL> edit
  1  select first_name,department_id,count(*) over(partition by department_id) as cnt
  2  from employees
  3* order by 2
SQL> /

FIRST_NAME           DEPARTMENT_ID        CNT                                   
-------------------- ------------- ----------                                   
Jennifer                        10          1                                   
Michael                         20          2                                   
Pat                             20          2                                   
Den                             30          6                                   
Alexander                       30          6                                   
Shelli                          30          6                                   
Sigal                           30          6                                   
Guy                             30          6                                   
Karen                           30          6                                   
Susan                           40          1                                   
Matthew                         50         45    
。。。。。。。。。。
如上结果所示：对于同一个部门（同一个分区）的每个员工的cnt值相同，这是由于在遇到新部门之前不会重置聚集。

 

另外partition by字句的优点是：在同一个select语句中，一个窗口函数的计算独立于按其他列分区的其他窗口函数的计算。例如下面的查询，返回每个员工、他的部门、他的部门中的员工数、他的职位以及跟他相同职位的员工数：

 

 

 1  select first_name,department_id,count(*) over (partition by department_id) as dept_cnt,
  2  job_id,
  3  count(*) over(partition by job_id) as job_cnt
  4  from employees
  5* order by 2
SQL> /

FIRST_NAME           DEPARTMENT_ID   DEPT_CNT JOB_ID        JOB_CNT             
-------------------- ------------- ---------- ---------- ----------             
Jennifer                        10          1 AD_ASST             1             
Michael                         20          2 MK_MAN              1             
Pat                             20          2 MK_REP              1             
Sigal                           30          6 PU_CLERK            5             
Alexander                       30          6 PU_CLERK            5             
Shelli                          30          6 PU_CLERK            5             
Karen                           30          6 PU_CLERK            5             
Den                             30          6 PU_MAN              1             
Guy                             30          6 PU_CLERK            5             
Susan                           40          1 HR_REP              1             
Donald                          50         45 SH_CLERK           20

 

 

  1. 框架字句：

当在窗口函数over字句中使用order by 字句时，就指定了两件事：

1、分区中的行如何排序

2、在计算中包含哪些行

请看下面的查询，它计算了30号员工的工资的累计和

 1  select department_id,first_name,hire_date,salary,
  2  sum(salary) over(partition by department_id) as total1,
  3  sum(salary) over() as total2,
  4  sum(salary) over(order by hire_date) as running_total
  5  from employees
  6* where department_id=30
SQL> /

DEPARTMENT_ID FIRST_NAME           HIRE_DATE          SALARY     TOTAL1         
------------- -------------------- -------------- ---------- ----------         
    TOTAL2 RUNNING_TOTAL                                                        
---------- -------------                                                        
           30 Den                  07-12月-02          11000      24900         
     24900         11000                                                        
                                                                                
           30 Alexander            18-5月 -03           3100      24900         
     24900         14100                                                        
                                                                                
           30 Sigal                24-7月 -05           2800      24900         
     24900         16900                                                        
                                                                                

DEPARTMENT_ID FIRST_NAME           HIRE_DATE          SALARY     TOTAL1         
------------- -------------------- -------------- ---------- ----------         
    TOTAL2 RUNNING_TOTAL                                                        
---------- -------------                                                        
           30 Shelli               24-12月-05           2900      24900         
     24900         19800                                                        
                                                                                
           30 Guy                  15-11月-06           2600      24900         
     24900         22400                                                        
                                                                                
           30 Karen                10-8月 -07           2500      24900         
     24900         24900                                                        
                                                                                

已选择6行。

上面的查询语句相当于：


 

  1  select department_id,first_name,hire_date,salary,
  2  sum(salary) over(partition by department_id) as total1,
  3  sum(salary) over() as total2,
  4  sum(salary) over(order by hire_date range between unbounded preceding and current row) as running_total
  5  from employees
  6* where department_id=30

 

 

也就说默认情况下会告诉查询：计算所有行的和，即从当前行开始、包括它前面的所有行。对从当前行开始、包括它前面的所有行进行求和，就可以得到累计和效果了。

 

通过，框架字句允许定义数据的不同“子窗口”，以便在计算中使用，有很多方式可以指定这样的子窗口。如：

 1  select department_id,first_name,salary,
  2  sum(salary) over (order by hire_date range between unbounded preceding and current row) as run_total1,
  3  sum(salary) over(order by hire_date rows between 1 preceding and current row) as run_total2,
  4  sum(salary) over(order by hire_date range between current row and unbounded following) as run_total3,
  5  sum(salary) over(order by hire_date rows between current row and 1 following) as run_total4
  6  from employees
  7* where department_id=30
SQL> /

DEPARTMENT_ID FIRST_NAME               SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3  
------------- -------------------- ---------- ---------- ---------- ----------  
RUN_TOTAL4                                                                      
----------                                                                      
           30 Den                       11000      11000      11000      24900  
     14100                                                                      
                                                                                
           30 Alexander                  3100      14100      14100      13900  
      5900                                                                      
                                                                                
           30 Sigal                      2800      16900       5900      10800  
      5700                                                                      
                                                                                

DEPARTMENT_ID FIRST_NAME               SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3  
------------- -------------------- ---------- ---------- ---------- ----------  
RUN_TOTAL4                                                                      
----------                                                                      
           30 Shelli                     2900      19800       5700       8000  
      5500                                                                      
                                                                                
           30 Guy                        2600      22400       5500       5100  
      5100                                                                      
                                                                                
           30 Karen                      2500      24900       5100       2500  
      2500                                                                      
                                                                                

已选择6行。

 

其中：

range between unbounded preceding and current row 指定计算当前行开始、当前行之前的所有值；

rows between 1 preceding and current row 指定计算当前行的前一行开始，其范围一直延续到当前行；

range between current row and unbounded following 指定计算从当前行开始，包括它后面的所有行；

rows between current row and 1 following 指定计算当前行和它后面的一行；

 

最后一个例子，展示 了框架字句对查询输出的影响，请看下面查询：

 1  select first_name,salary,min(salary) over(order by salary) min1,
  2  max(salary) over(order by salary) max1,
  3  min(salary) over(order by salary range between unbounded preceding and unbounded following) min2,
  4  max(salary) over(order by salary range between unbounded preceding and unbounded following) max2,
  5  min(salary) over(order by salary range between current row and current row) min3,
  6  max(salary) over(order by salary range between current row and current row) max3,
  7  max(salary) over(order by salary rows between 3 preceding and 3 following) max4
  8* from employees
SQL> /

FIRST_NAME               SALARY       MIN1       MAX1       MIN2       MAX2     
-------------------- ---------- ---------- ---------- ---------- ----------     
      MIN3       MAX3       MAX4                                                
---------- ---------- ----------                                                
TJ                         2100       2100       2100       2100      24000     
      2100       2100       2400                                                
                                                                                
Steven                     2200       2100       2200       2100      24000     
      2200       2200       2400                                                
                                                                                
Hazel                      2200       2100       2200       2100      24000     
      2200       2200       2500      
 
请仔细观察计算结果，领会子窗口的内涵；
 
参见：SQL CookBook

Text2SQL中反思纠错的实战方案
herosunly的博客
06-29 20万+
本文主要介绍了Text2SQL中反思纠错的实战方案，希望对学习大语言模型的同学们有所帮助。 文章目录 1. 前言 2. 反思纠错的实战方案
基于大模型的Text2SQL微调的实战教程(二)
热门推荐
herosunly的博客
04-23 19万+
本文主要介绍了基于大模型的Text2SQL微调的实战教程(二)，希望对学习大语言模型的同学们有所帮助。 文章目录 1. 前言 2. 配置环境 2.1 安装虚拟环境 2.2 安装依赖库 2.3 下载模型文件 3. 运行代码 3.1 数据预处理 3.2 修改配置文件 3.3 微调
Oracle开发专题之：OLAP 函数 （rows 2 preceding / unbounded preceding）
weixin_30570101的博客
02-17 237
ORACLE OLAP 函数 　　 最近这个东东用得特别多，总结了一下 。 　　 语法: FUNCTION_NAME(,,...) 　　 OVER() 　　OLAP函数语法四个部分: 　　1、function本身 用于对窗口中的数据进行操作； 　　2、partitioning clause 用于将结果集分区； 　　3、order by clause 用于对分区...
oracle两个人之间的工资之差,史上最难oracle数据库练习题（附答案）
weixin_35448535的博客
04-13 494
oracle数据库练习题，所有练习数据都来自于安装oracle数据库后，自带的那几张表，EMP,DEPT等。部分题目答案已给出，若有异议可以私聊一起探讨。第一题:统计薪资大于 薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息。最终答案：select * from emp where sal >( select avg (sal) from emp w...
常见的sql查询语句
nuliPerfect的博客
07-30 2662
NUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中，P表示数字的总位数；S表示小数点后面的位数。 在表student中的id列和fee列的定义如下： CREATE TABLE student(    id NUMBER(4),    name VARCHAR2(20),    gender CHAR(1),    fee NUMBER(7,2)); 上述定义表示id列中的
Hive 开窗函数 rows between current row and unbounded following 的使用
qq_42155078的博客
03-14 5017
1.rows between的作用 在使用hsql完成一些业务需求的时候,我们经常会用到窗口函数,某些场景 下会涉及到需要控制窗口函数的范围来满足业务需求,这个时候就需要用到rows between了,使用rows between可以根据自己的需求任意的控制窗口函数的范围。 2.模拟数据，建表 create external table if not exists t_sale_info ( sid bigint , day_time string , sales_volume bigin
HiveSQL题——前后函数(lag/lead)
爱吃辣条的博客
01-29 4011
HiveSQL题——前后函数(lag/lead)
数据库实战把一些工作中遇到的以及在网上找到的面试题进行了sql语句自己编辑以及测试.zip
最新发布
05-05
数据库实战把一些工作中遇到的以及在网上找到的面试题进行了sql语句自己编辑以及测试.zip数据库实战把一些工作中遇到的以及在网上找到的面试题进行了sql语句自己编辑以及测试.zip数据库实战把一些工作中遇到的以及在...
SQL Server实时同步更新远程数据库遇到的问题小结
01-21
工作中遇到这样的情况，需要在更新表TableA（位于服务器ServerA 172.16.8.100中的库DatabaseA）同时更新TableB（位于服务器ServerB 172.16.8.101中的库DatabaseB）。 TableA与TableB结构相同，但数据数量不一定相同...
QT与SQL（二）：数据库中文信息读取乱码
GreenHand的博客
11-27 2879
最近，在使用QT的QODBC进行SQL 2008 R2数据库的通信连接时，遇到了一个问题，这里就将该问题进行一下记录。 访问Microsoft SQL Server 2008R2数据库内的一张数据表，表中信息除了数字之外，还有一部分中文信息，中文信息的数据类型设置为nvchar(50)，在数据库中的存储情况如下： 尝试对该信息进行读取，读取代码如下： qDebug()<<"...
SQL Server窗口框架——ROWS、RANGE
weixin_34124651的博客
04-17 280
说到窗口框架就不得不提起开窗函数。 开窗函数支持分区、排序和框架三种元素，其语法格式如下： 1 OVER ( 2 [ <PARTITION BY clause> ] 3 [ <ORDER BY clause> ] 4 [ <ROW or RANGE clause> ] 5 ...
rows between unbounded preceding and current row等，unbounded ，preceding ，following，current row含义详解
卡奥斯道的博客
09-30 3万+
unbounded：无界限 preceding：从分区第一行头开始，则为 unbounded。 N为：相对当前行向前的偏移量 following ：与preceding相反,到该分区结束，则为 unbounded。N为：相对当前行向后的偏移量 current row：顾名思义，当前行，偏移量为0 例子： partition by order by asc/desc rows ...
Hive常见开窗函数的实际应用
数据与算法架构提升之路专栏
04-08 658
在15道Hive SQL经典面试题里我们对hive的实战常用法，有了较为全面的认知。本文将围绕开窗函数进行升入的探讨
3.5 Window Functions
在路上
12-16 499
3.5窗口函数 窗口函数针对当前行，对一组表行进行计算。这类似于使用聚合函数进行的计算。然而，窗口函数不会像非窗口函数那样将行分组聚合为一行输出，而是这些行保留各自的属性。而且，窗口函数能够不仅仅访问查询结果的当前行。 以下示例，展示了雇员如何与本部门的平均工资进行比较： mydb=# create table empsalary(depname varchar(20),empno int,salary numeric); CREATE TABLE mydb=# insert int...
记录一次未设置数据库连接池超时回收引发的血案
Mars_xiaotao的博客
04-27 1293
记录一次未设置数据库连接池超时回收引发的血案 Java程序员渣渣进阶者，大神们多多指教 由于该项目是内网项目，无法远程，运维方是伙伴公司，现场运维截取到如下报错信息 从图片可以看出，请求获取不到数据库连接，系统处于卡死状态。然而系统设定数据库最大连接数为500，最大连接数也就是系统最大并发数，好像一般设定100就够了（好像设置太大了也不好，参考链接：你真的知道如何设置数据库连接池的大小吗？数据库连接池到底应该设置多大？），该系统用户数又只有两三千人，应该足够用了（参考：在线用户和并发用户的比例关系）。 继
Druid runningSqlCount 1 线上解决思路
BIG.KE的博客
12-01 3378
1. 线上发现 runningSqlCount 1 : SELECT bc.created, sp 报错, 抛了一大堆SQL日志, 日志文件都被塞满了, 查询baidu.com 以后, 发现是因为数据库连接不够用了, 有了思路以后, 立马去查看druid控制台 2. druid 控制台 http://xxxx/druid/datasource.html 查看数据源配置以后, 发现最大连接数为20, 就去查看, 到的是什么原因导致20个连接都不够用 2.1. 查看 Druid SQL 监控 发现SQ
MYSQL学习笔记（6）
A133333的博客
03-25 974
一些补充知识 replace 例题 问题分析 过程重写 细节问题 rows/range RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义; “RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围，这个子句又被称为定位框架。 使用ROWS选项定义当前行的固定前后记录。这里看到的行取决于窗口的ORDER BY从句，也可以说在物理级别定义的窗口； RANGE 是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列
ora-30485:在窗口说明中丢失order by表达式
如风
02-17 6813
在java程序代码中用到orace函数row_number() over，但是会报 ora-30485:在窗口说明中丢失order by表达式　错误，但是在pl/sql中不会报错，在网上查了很多关于此问题的解决方法，但是都无用，因为我在此函数中添加了order by 表达式。后来经过多次测试，在sql语句最后加一个order by 即可完美解决。由此得出结论，报此错误基本有两种解决方法： １.在...
hive常用方法函数作用总结
逐梦的博客
04-12 713
字符串 concat（str1，str2） 字符串拼接 regexp_replace(string A, string B, string C) 字符串替换函数，将字符串A中的B用C替换。 repeat(string str, int n) 重复N次字符串 lpad(string str, int len, string pad)将字符串str用pad进行左补足到len位（rpa...
在.net中实现DataTable批量更新SQL Server数据库
如果在执行过程中遇到任何错误，可以回滚事务，以保持数据库状态的一致性。 ### 关键代码示例 以下是一段简化的示例代码，展示了如何使用C#和.NET框架批量插入DataTable数据到SQL Server： ```csharp using ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值