窗口函数案例练习（二）

第二题：

数据：

创建表格：
create table test_window2
(name VARCHAR(20),
orderdate VARCHAR(20),
cost int);

更改列的字段格式：
alter table test_window2 modify column orderdate  DATE;

注意输入日期的时候要加引号：
Insert into test_window2 values
('jack','2017-01-01',10)
,('tony','2017-01-02',15)
,('jack','2017-02-03',23)
,('tony','2017-01-04',29)
,('jack','2017-01-05',46)
,('jack','2017-04-06',42)
,('tony','2017-01-07',50)
,('jack','2017-01-08',55)
,('mart','2017-04-08',62)
,('mart','2017-04-09',68)
,('neil','2017-05-10',12)
,('mart','2017-04-11',75)
,('neil','2017-06-12',80)
,('mart','2017-04-13',94);

操作练习：
1）查询在2017年4月份购买过的顾客及总人数
2）查询顾客的购买明细及月购买总额
3）查询顾客的购买明细及到目前为止每个顾客购买总金额
4）查询顾客上次的购买时间----lag(x,n)over()和lead(x,n)over()偏移量分析函数的运用
5）查询前20%时间的订单信息----ntile(n)

1.1 查询在2017年4月份购买过的顾客及总人数

1)加order by

SELECT
    *,
    count(*) over ( ORDER BY cost ) 
FROM
    test_window2 
WHERE
    SUBSTR( orderdate, 1, 7 )= '2017-04'

2)不加加order by

SELECT
    *,
    count(*) over () 
FROM
    test_window2 
WHERE
    SUBSTR( orderdate, 1, 7 )= '2017-04'

看总人数时不能加order by，加order by之后，会显示排序顺序，而不会直接显示总人数

加order by的 不加order by的

1.2 查询顾客的购买明细及月购买总额

SELECT
    *,
    SUM( COST ) OVER (
        PARTITION BY NAME,
    SUBSTR( orderdate, 1, 7 )) 
FROM
    test_window2

1.3 查询顾客的购买明细及到目前为止每个顾客购买总金额

SELECT
    *,
    SUM( COST ) OVER ( PARTITION BY NAME rows BETWEEN unbounded preceding AND current ROW ) 
FROM
    test_window2

1.4 查询顾客上次的购买时间----lag(x,n)over()偏移量分析函数的运用

SELECT
    *,
    lag ( orderdate, 1 ) OVER ( PARTITION BY NAME ) AS lastdate 
FROM
    test_window2

-- 查询顾客上次的购买时间----lead(x,n)over()偏移量分析函数的运用

SELECT
    *,
    lead ( orderdate, 1 ) OVER ( PARTITION BY NAME ) AS lastdate 
FROM
    test_window2

lag(x,n)
lead(x,n)

5）查询前20%时间的订单信息

SELECT
    * 
FROM
    ( SELECT *, ntile ( 5 ) OVER ( ORDER BY orderdate ) AS top FROM test_window2 ) AS t 
WHERE
    t.top =1


数据库里无法直接得到百分数，只能采用拼接的方式，但是拼接之后就无法进行数学运算了

SELECT *    
FROM
(SELECT *
,concat(round((PERCENT_RANK() OVER ( ORDER BY orderdate ))*100,2),'%')as top
FROM test_window2) as t
WHERE t.top<=20% --此方式是错的