mysql 分位数滑动窗口函数数据分析

xiaogg3678

于 2023-05-08 17:23:15 发布

阅读量171

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/xiaogg3678/article/details/130563564

版权


-- 1 滑动窗口示例 - 平均值
SELECT
	* 
FROM
	(
	SELECT
		id,
		order_money,
		member_id,
		create_time,
		avg( order_money ) over w AS avg_num,
		sum( order_money ) over w AS sum_num 
	FROM
	dm_order_2018 window w AS ( PARTITION BY member_id ORDER BY create_time DESC rows BETWEEN 1 preceding AND 0 following )) t;
	
SELECT
t2.SecuCode ,t2.SecuAbbr ,
	t1.* 
FROM
	(
 select
    ID,
    InnerCode,
    tradingDay,
		PE pe,PSTTM,PCFTTM,PBLF,
    avg(PE) over w AS AVG_PE,
		avg(PSTTM) over w AS AVG_PSTTM,
		avg(PCFTTM) over w AS AVG_PCFTTM,
		avg(PBLF) over w AS AVG_PBLF
	from
    DZ_DIndicesForValuation window w AS (PARTITION BY InnerCode  ORDER BY TradingDay  rows   BETWEEN 365 preceding AND 0 following)  ) t1
		left join secumain t2 on t1.InnerCode = t2.InnerCode 
where t1.InnerCode='3'   and t1.TradingDay >= DATE_SUB(now(), INTERVAL 1 YEAR)
order by
    t1.TradingDay 
	
	select avg(PE) from DZ_DIndicesForValuation t1 where    t1.TradingDay >= DATE_SUB('2023-05-08', INTERVAL 1 YEAR)  and t1.InnerCode='3'
	
	-- 2 查看数据的分位数 
SELECT
* 
FROM
	(
	
	SELECT
		id,
		order_money,
		member_id,
		create_time,
		row_number() over w AS row_num,
		percent_rank() over w AS percent 
FROM
	dm_order_2018 window w AS ( PARTITION BY member_id ORDER BY create_time DESC )
	
	) t;
	
	
SELECT
t2.SecuCode ,t2.SecuAbbr ,
	t1.* 
FROM
	(	
	select
    InnerCode,
    TradingDay,
    PE,
		row_number() over w AS row_num,
		percent_rank() over w AS percent 
from
    DZ_DIndicesForValuation  window w AS (PARTITION BY InnerCode  ORDER BY PE  rows   BETWEEN 365 preceding AND 0 following) ) t1
  	left join secumain t2 on t1.InnerCode = t2.InnerCode 
where t1.InnerCode='3'   and t1.TradingDay >= DATE_SUB(now(), INTERVAL 1 YEAR)  -- and t1.row_num=364*0.75
order by
    t1.PE 
		
		
SELECT
	* 
FROM
	( SELECT id, order_money, member_id, create_time, row_number() over ( PARTITION BY member_id ORDER BY order_money DESC ) AS row_num FROM dm_order_2018 ) t

CREATE TABLE dm_order_2018(  id varchar(100) DEFAULT NULL COMMENT '订单id',  order_money float DEFAULT NULL COMMENT '订单金额',  member_id int(11) DEFAULT NULL COMMENT '会员id',  create_time timestamp DEFAULT NULL COMMENT '创建时间',  status int(11) DEFAULT NULL COMMENT '订单状态|0：取消，1：待支付，2:付款成功');

INSERT INTO dm_order_2018 VALUES ('A001', '100.15', '1', '2018-01-01 13:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A023', '100.15', '1', '2018-01-01 15:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A002', '100', '2', '2018-04-01 14:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A003', '12.1', '3', '2018-01-01 13:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A004', '200.15', '4', '2018-01-01 13:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A005', '1200.15', '5', '2018-01-01 13:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A006', '0.15', '11', '2018-01-01 17:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A007', '1215.1', '31', '2018-01-01 10:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A008', '100.75', '15', '2018-01-01 14:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A009', '100.15', '8', '2018-01-01 15:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A010', '20.15', '9', '2018-01-01 16:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A011', '110.15', '21', '2018-01-01 14:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A012', '1.15', '13', '2018-01-01 13:03:12', '2');INSERT INTO dm_order_2018 VALUES ('A013', '20.15', '14', '2018-02-01 13:53:12', '2');INSERT INTO dm_order_2018 VALUES ('A014', '30.15', '15', '2018-03-01 13:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A015', '40.5', '13', '2018-01-01 13:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A016', '65', '1', '2017-01-01 13:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A017', '78.15', '1', '2017-03-01 09:23:12', '1');INSERT INTO dm_order_2018 VALUES ('A018', '88.15', '1', '2017-11-01 20:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A019', '99', '12', '2018-02-11 13:23:12', '1');INSERT INTO dm_order_2018 VALUES ('A020', '10.3', '13', '2018-04-01 13:23:12', '2');INSERT INTO dm_order_2018 VALUES ('A021', '600', '5', '2018-11-01 08:58:31', '2');INSERT INTO dm_order_2018 VALUES ('A022', '500', '31', '2018-11-11 08:59:02', '2');

(1)按用户id分组，消费金额进行降序排序

select * FROM ( select id, order_money, member_id, create_time,       row_number() over(partition by member_id ORDER BY order_money desc) as row_num  from dm_order_2018) t