专栏首页SQL实现SQL 分析大盘走势

SQL 分析大盘走势

下表(stock)记录了某指数过去一段时间的收盘价,我们要从这张表中找出收盘价持续上涨的日期。

deal_date    price  
----------  --------
2020-11-20      3377
2020-11-23      3414
2020-11-24      3402
2020-11-25      3362
2020-11-26      3369
2020-11-27      3408
2020-11-30      3391
2020-12-01      3451
2020-12-02      3449

期望得到的结果 >>>

deal_date                min_price  max_price  
-----------------------  ---------  -----------
2020-11-20 ~ 2020-11-23       3377         3414
2020-11-25 ~ 2020-11-27       3362         3408
2020-11-30 ~ 2020-12-01       3391         3451

一种常规的解决思路:

将当前行的值和上一行的值作比较,我们就能知道当前行的值是增加了还是减少了,或者保持不变。 那怎么把单调递增的序列归到同一组呢?给序列中的第一个值分配唯一的序号,序列中的其它值的序号保持和第一个值的序号一致。

在 SQL 中,使用窗口函数 lag() 可以在当前行获取到上一行某个字段的值。窗口函数 sum() 可以实现累加的操作,要给满足某种规则下的多行数据分配同一个序号,只需要给这些数据行增加一个新字段,对于新字段,除了第一行的值是目标序号,其它行的值都为 0 ,然后对新字段做累加操作就能做到同一个序列的序号一致。

完整的 SQL :

--获取前一行的 price
with x0 AS 
(SELECT 
  *,
  lag (price) over (
ORDER BY deal_date) AS last_price 
FROM
  stock),
-- 如果是递增,则将标识字段设置为 0,其它的设置为 1
x1 AS 
(SELECT 
  *,
  IF(price - last_price > 0, 0, 1) AS inc 
FROM
  x0),
-- 累加标识字段,将递增序列的行归为一组
x2 AS 
(SELECT 
  *,
  SUM(inc) over (
ORDER BY deal_date) AS g 
FROM
  x1),
-- 过滤掉非单调递增的组(只有一行数据)
x3 AS 
(SELECT 
  g,
  CONCAT_WS(' ~ ', MIN(deal_date), MAX(deal_date)) AS deal_date,
  MIN(price) AS min_price,
  MAX(price) AS max_price 
FROM
  x2 
GROUP BY g 
HAVING COUNT(*) >= 2) 
SELECT 
  deal_date,
  min_price,
  max_price 
FROM
  x3 

另一种非常规的思路:

找到所有日期的组合数据,筛选较后的交易日的价格大于较前的交易日的价格的组合。如果两个交易日之间的所有交易日期的价格都是单调递增的,则这是一个单调递增组合。最后,剔除那些交易日期被包含的组合。

获取之后的交易日的价格比之前的交易日的价格高的所有组合:

SELECT 
  a.deal_date AS start_date,
  b.deal_date AS end_date,
  a.price AS start_price,
  b.price AS end_price 
FROM
  stock a 
  INNER JOIN stock b 
    ON b.deal_date > a.deal_date 
    AND a.price < b.price 
ORDER BY a.deal_date,
  b.deal_date;
  
-- 数据太多,只放了部分结果

start_date  end_date    start_price  end_price  
----------  ----------  -----------  -----------
2020-11-20  2020-11-23         3377         3414
2020-11-20  2020-11-24         3377         3402
2020-11-20  2020-11-27         3377         3408
2020-11-20  2020-11-30         3377         3391
2020-11-20  2020-12-01         3377         3451
2020-11-20  2020-12-02         3377         3449
2020-11-23  2020-12-01         3414         3451
2020-11-23  2020-12-02         3414         3449
...
2020-11-30  2020-12-01         3391         3451
2020-11-30  2020-12-02         3391         3449

对于"2020-11-20 ~ 2020-12-02" 这个组合,怎么知道这个组合之间的所有交易日期的价格是否都是单调递增的呢?

任意取出在这个组合内的两个交易日的价格,如果这两个交易日期的价格不在这个组合的价格之内,或者交易日期在前的价格大于等于交易日期靠后的价格,则这个组合不是单调递增的。

2020-12-01 的价格是 3451,大于 2020-12-02 的价格,因此"2020-11-20 ~ 2020-12-02" 不是价格单调递增的组合。

完整的 SQL :

x0 AS 
(SELECT 
  a.deal_date AS start_date,
  b.deal_date AS end_date,
  a.price AS start_price,
  b.price AS end_price 
FROM
  stock a 
  INNER JOIN stock b 
    ON b.deal_date > a.deal_date 
    AND a.price < b.price 
    AND NOT EXISTS 
    (SELECT 
      NULL 
    FROM
      stock c,
      stock d 
    -- c 和 d 的交易日在 a 和 b 的交易日期的范围内 
    WHERE c.deal_date > a.deal_date 
      AND c.deal_date < b.deal_date 
      AND d.deal_date > a.deal_date 
      AND d.deal_date < b.deal_date 
    -- 非单调递增的判断规则
      AND (
        (
          d.deal_date > c.deal_date 
          AND d.price <= c.price
        ) 
        OR (
          c.price <= a.price 
          OR c.price >= b.price
        ) 
        OR (
          d.price <= a.price 
          OR d.price >= b.price
        )
      ))) 
SELECT 
  MIN(start_date) AS start_date,
  end_date,
  MIN(start_price) AS start_price,
  MAX(end_price) AS end_price 
FROM
  (SELECT 
    start_date,
    MAX(end_date) AS end_date,
    MIN(start_price) AS start_price,
    MAX(end_price) AS end_price 
  FROM
    x0 
  GROUP BY start_date) x1 
GROUP BY end_date 

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:SQL 后花园

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-12-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 盘点大数据分析领域五大趋势

      目前,大数据分析是一个非常热门的行业,一夜间,似乎企业的数据已经价值连城。企业都在开始尝试利用大数据来增强自己的企业业务竞争力,但是对于大数据分析行业来说...

    腾讯研究院
  • 面试:mysql最全索引与优化详解

    mysql 是我们最常用的数据存储的的程序,它是关系数据库的代表,可以直接服务于我们的常规业务,是我们不能离开的数据存储器,对于关系操作复杂的业务,具有很强的优...

    黑白格
  • 十一大市场发展趋势主导高级分析走向

    大数据文摘
  • 又涨了!PostgreSQL 这是要赶超 MySQL 的节奏?

    DB-Engines 发布了 2017 年 12 月份的数据库排名。排前 20 名的数据库中,Oracle 稳居第一,排名在第 9 名的 Cassandra 赶...

    企鹅号小编
  • 数据分析师发展的五大问题,一次扫清!

    马上就是2020年了,新年将至,很多同学都在思考职业发展路径问题。最近咨询陈老师的也很多,一个很普遍被问到的,就是:数据分析师的红旗,到底能打多久?今天集中解答...

    接地气的陈老师
  • 大数据 | Spark的现状与未来发展

    Spark的发展 对于一个具有相当技术门槛与复杂度的平台,Spark从诞生到正式版本的成熟,经历的时间如此之短,让人感到惊诧。2009年,Spark诞生于伯克利...

    张逸
  • 数据分析入门系列教程-股票走势预测分析

    今天我们做一个关于股票的小项目--预测股票走势。首先要声明下,股市有风险,购买需谨慎啊!股票作为金融体系的一员,其走势收到了多方面的影响,并不是能够通过一两个算...

    周萝卜
  • MySQL的使用及优化

    最近听了公司里的同事做的技术分享,然后觉得对自己还是挺有帮助的。都是一些日常需要注意的地方,我们目前在开发过程中,其实用不到MySQL太深的内容的。只是能适用我...

    纪莫
  • 微信ANDROID客户端-会话速度提升70%的背后

    image.png 背景 打开会话速度慢 在同一个会话有较多的历史消息下,各种查询,更新,删除等操作,速度明显下降。 在会话内有较大量历史消息情况下,进入...

    微信终端开发团队
  • 被敖丙用烂的「数据库调优」连招?真香,淦!

    哈哈开头这个场景是我臆想的一个面试场景,但是大家是不是觉得很真实,每个人的简历上但凡写到了数据库,都会在后面顺便写一句,会数据库调优。

    敖丙
  • 大数据分析的八大趋势

    Intuit数据工程副主管Loconzolo双脚都已经迈进数据湖里了。Smarter Remarketer首席数据科学家DeanAbbott也为云技术的发展指...

    腾讯研究院
  • 大数据分析的八大趋势

    Intuit数据工程副主管Loconzolo双脚都已经迈进数据湖里了。Smarter Remarketer首席数据科学家DeanAbbott也为云技术的发展指出...

    CSDN技术头条
  • 为什么大家都说“SELECT *”效率低?

    无论在工作还是面试中,关于 SQL 中不要用“SELECT *”,都是大家听烂了的问题,虽说听烂了,但普遍理解还是在很浅的层面,并没有多少人去追根究底,探究其原...

    架构师修炼
  • MySQL 慢日志线上问题分析及功能优化

    MySQL 慢日志(slow log)是 MySQL DBA 及其他开发、运维人员需经常关注的一类信息。使用慢日志可找出执行时间较长或未走索引等 SQL 语句,...

    吴生
  • 构建实时数仓 - 当 TiDB 偶遇 Pravega

    数据仓库是公司数据发展到一定规模后必然需要提供的一种基础服务,也是“数据智能”建设的基础环节。早期数仓多为离线模式,主要处理的是 T+1 的数据,随着互联网时代...

    PingCAP
  • 解读2018:13家开源框架谁能统一流计算?

    AI 前线导读:2018 年接近尾声,AI 前线策划了“解读 2018”年终技术盘点系列文章,希望能够给读者清晰地梳理出重要技术领域在这一年来的发展和变化。本...

    Fayson
  • 股市行情指标计算原理和趋势反映--量化交易1-基础

    量能也成为成交量,代表多空双方交战的过程,一定程度上决定了价格和走势。量能和价格走势进行结合,成为量价结合的分析方法。

    嘘、小点声
  • awr性能问题排查第一篇(r3笔记第42天)

    对于awr,里面涵盖的内容比较杂,有时候看报告的时候总是不知道该怎么下手。时间长了,可能会有一些阅读习惯或者心得。今天在看大师chris lawson的一篇博文...

    jeanron100
  • OLAP计算引擎怎么选?

    大家好,我是一哥,今天聊一聊OLAP技术,一哥认为好的OLAP引擎应该具备以下三个条件:易开发、易维护、易移植。今天给大家分享一下常见的几种OLAP计算引擎,他...

    数据社

扫码关注云+社区

领取腾讯云代金券