Oracle求每只股票连续下跌的天数（以股票数据为例）附上建表语句和数据，直接就能测试

最新推荐文章于 2024-06-12 17:19:11 发布

木及言风

最新推荐文章于 2024-06-12 17:19:11 发布

阅读量897

点赞数

文章标签：数据库开发 oracle sql 数据库大数据

本文链接：https://blog.youkuaiyun.com/weixin_56496488/article/details/124288078

版权

博客介绍了如何在Oracle数据库中使用SQL查询每只股票连续下跌的天数，提供了建表语句和模拟数据，便于测试。示例代码中，通过特定逻辑计算连续下跌的天数，适用于处理非连续时间序列数据。鼓励读者根据需求优化这段匆忙编写的SQL语句。

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

股票的交易日期是不连续的，这个有点小难受，先建表

create table TT
(
  ts_code         VARCHAR2(20),
  trade_date      VARCHAR2(20),
  open_price      NUMBER,
  high_price      NUMBER,
  low_price       NUMBER,
  close_price     NUMBER,
  pre_close_price NUMBER,
  change_value    NUMBER,
  pct_chg         NUMBER,
  vol             NUMBER,
  amount          NUMBER,
  id              VARCHAR2(32) default sys_guid()
);
comment on table TT
  is 'A股历史交易数据';
-- Add comments to the columns 
comment on column TT.ts_code
  is '股票代码';
comment on column TT.trade_date
  is '交易日期';
comment on column TT.open_price
  is '开盘价';
comment on column TT.high_price
  is '最高价';
comment on column TT.low_price
  is '最低价';
comment on column TT.close_price
  is '收盘价';
comment on column TT.pre_close_price
  is '昨收价';
comment on column TT.change_value
  is '涨跌额';
comment on column TT.pct_chg
  is '涨跌幅';
comment on column TT.vol
  is '成交量（手）';
comment on column TT.amount
  is '成交额（千元）';

股票数据太多，为了方便测试，我就提取了两只股票的一个自然月的交易数据


insert into tt (TS_CODE, TRADE_DATE, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE, PRE_CLOSE_PRICE, CHANGE_VALUE, PCT_CHG, VOL, AMOUNT, ID)
values ('002473.SZ', '20220412', 10.11, 10.11, 10.11, 10.11, 10.64, -0.53, -4.98, 3483, 3521.31, 'C06A63B57A7340B384CC9A5EFB352A21');

insert into tt (TS_CODE, TRADE_DATE, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE, PRE_CLOSE_PRICE, CHANGE_VALUE, PCT_CHG, VOL, AMOUNT, ID)
values ('002473.SZ', '20220408', 11.78, 11.78, 11.19, 11.2, 11.78, -0.58, -4.92, 15060.25, 16955.22, 'E6E151CDD6864DF3B27FDB38A13459EE');

insert into tt (TS_CODE, TRADE_DATE, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE, PRE_CLOSE_PRICE, CHANGE_VALUE, PCT_CHG, VOL, AMOUNT, ID)
values ('002473.SZ', '20220407', 12.26, 12.26, 11.67, 11.78, 12.28, -0.5, -4.07, 27691, 32828.14, 'E435869FB9C848C69AC23AC01C69A91F');

insert into tt (TS_CODE, TRADE_DATE, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE, PRE_CLOSE_PRICE, CHANGE_VALUE, PCT_CHG, VOL, AMOUNT, ID)
values ('002473.SZ', '20220401', 11.6, 12.11, 11.58, 12.11, 11.53, 0.58, 5.03, 15104, 18108.01, '09DB34AEF7C34216B52914482CDB178B');

insert into tt (TS_CODE, TRADE_DATE, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE, PRE_CLOSE_PRICE, CHANGE_VALUE, PCT_CHG, VOL, AMOUNT, ID)
values ('002473.SZ', '20220330', 10.78, 11.35, 10.78, 10.99, 11.35, -0.36, -3.17, 40689, 44213.89, '040AFE22B69C4D64BD2EBAC62FE9F340');

insert into tt (TS_CODE, TRADE_DATE, OPEN_PRIC

最低0.47元/天解锁文章