UnicornLee opened a new issue, #18081: URL: https://github.com/apache/doris/issues/18081
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description 1. 某些场景下SQL语句中需要使用变量完成某些操作，这在MySQL中是很普通的操作。 2. 但是目前Doris中SQL语句中使用变量是不支持的，希望能支持该功能。 ### Use case 1. 建表SQL ```sql drop table if exists `trad_sk_daily_jc`; CREATE TABLE `trad_sk_daily_jc` ( `EID` decimalv3(18, 0) NOT NULL, `ESEQID` decimalv3(29, 0) NOT NULL, `EITIME` datetimev2 NOT NULL, `EUTIME` datetimev2 NOT NULL DEFAULT CURRENT_TIMESTAMP, `EGETTIME` datetimev2 NOT NULL, `EISDEL` char(1) DEFAULT '0', `LOW` decimalv3(20, 8) DEFAULT NULL, `NEW` decimalv3(20, 8) DEFAULT NULL, `TVAL` decimalv3(20, 8) DEFAULT NULL, `HIGH` decimalv3(20, 8) DEFAULT NULL, `TURNRATE` decimalv3(20, 8) DEFAULT NULL, `OPEN` decimalv3(20, 8) DEFAULT NULL, `TVOL` decimalv3(20, 8) DEFAULT NULL, `PCHG` decimalv3(20, 8) DEFAULT NULL, `TRADEDATE` datetimev2 DEFAULT NULL, `TVALCNY` decimalv3(20, 8) DEFAULT NULL, `CHG` decimalv3(20, 8) DEFAULT NULL, `LCLOSE` decimalv3(20, 8) DEFAULT NULL, `BUYVOL` decimalv3(20, 8) DEFAULT NULL, `SELLVOL` decimalv3(20, 8) DEFAULT NULL, `SECURITYCODE` varchar(60) DEFAULT NULL, `CURRENCY` varchar(180) DEFAULT NULL, `TRADEMARKET` varchar(180) DEFAULT NULL, `SECURITYNAME` varchar(240) DEFAULT NULL, `SECINNERCODE` varchar(600) DEFAULT NULL, `TNUM` decimalv3(20, 8) DEFAULT NULL, `ESOURCEMEMO` varchar(300) DEFAULT NULL ) ENGINE = OLAP UNIQUE KEY(`EID`) DISTRIBUTED BY HASH(`EID`) BUCKETS AUTO PROPERTIES ( "replication_num" = "3", "light_schema_change" = "true" ); drop table if exists `cdsy_secucode`; CREATE TABLE `cdsy_secucode` ( `EID` decimalv3(18, 0) NOT NULL, `ESEQID` decimalv3(29, 0) NOT NULL, `EITIME` datetimev2 NOT NULL, `EUTIME` datetimev2 NOT NULL, `EGETTIME` datetimev2 NOT NULL, `EISDEL` char(1) DEFAULT '0', `USESTATE` char(2) DEFAULT NULL, `LISTDATE` datetimev2 DEFAULT NULL, `ENDDATE` datetimev2 DEFAULT NULL, `LISTSTATE` varchar(72) DEFAULT NULL, `CODETYPE` varchar(72) DEFAULT NULL, `SECURITYCODE` varchar(480) DEFAULT NULL, `CURRENCY` varchar(480) DEFAULT NULL, `SECINNERCODE` varchar(600) DEFAULT NULL, `SECURITYTYPE` varchar(1200) DEFAULT NULL, `COMPANYCODE` varchar(1200) DEFAULT NULL, `TRADEMARKETCODE` varchar(1200) DEFAULT NULL, `SECURITYTYPECODE` varchar(1200) DEFAULT NULL, `TRADEMARKET` varchar(1200) DEFAULT NULL, `SPELL` varchar(2400) DEFAULT NULL, `SECURITYSNAME` varchar(2400) DEFAULT NULL, `SECURITYNAME` varchar(4800) DEFAULT NULL, `ESOURCEMEMO` varchar(300) DEFAULT NULL, `EXPAND_NAME_ABBR` varchar(1200) DEFAULT NULL, `PREDICT_LTD` datetimev2 DEFAULT NULL, `EXPAND_NAME_PINYIN` varchar(300) DEFAULT NULL ) ENGINE = OLAP UNIQUE KEY(`EID`) DISTRIBUTED BY HASH(`EID`) BUCKETS AUTO PROPERTIES ( "replication_num" = "3", "light_schema_change" = "true" ); drop table if exists `trad_td_tdate`; CREATE TABLE `trad_td_tdate` ( `EID` decimalv3(18, 0) NOT NULL, `ESEQID` decimalv3(29, 0) NOT NULL, `EITIME` datetimev2 NOT NULL, `EUTIME` datetimev2 NOT NULL, `EGETTIME` datetimev2 NOT NULL, `EISDEL` char(1) DEFAULT '0', `PPDATE` datetimev2 DEFAULT NULL, `TRADEDATE` varchar(48) DEFAULT NULL, `CLOSETRADEPM` varchar(120) DEFAULT NULL, `AUCTIONCOPM` varchar(120) DEFAULT NULL, `AUCTIONOPAM` varchar(120) DEFAULT NULL, `AUCTIONOPPM` varchar(120) DEFAULT NULL, `OPENTRADEAM` varchar(120) DEFAULT NULL, `OPENTRADEPM` varchar(120) DEFAULT NULL, `AUCTIONCOAM` varchar(120) DEFAULT NULL, `CLOSETRADEAM` varchar(120) DEFAULT NULL, `TYPECODE` varchar(240) DEFAULT NULL, `TRADEMARKETCODE` varchar(600) DEFAULT NULL, `ESOURCEMEMO` varchar(300) DEFAULT NULL ) ENGINE = OLAP UNIQUE KEY(`EID`) DISTRIBUTED BY HASH(`EID`) BUCKETS AUTO PROPERTIES ( "replication_num" = "3", "light_schema_change" = "true" ); ``` 2. 测试SQL ```sql select securitycode, securitysname, dxl, gxl, tradedate from (select t.securitycode -- "证券代码" , t.securitysname , t.rn , t.tradedate , t.new , @dxl := case when t.rn = 1 and t.new < s.new then 1 when t.rn > 1 and t.new < s.new then @dxl + 1 else 0 end as dxl -- 低9序列 , @gxl := case when t.rn = 1 and t.new > s.new then 1 when t.rn > 1 and t.new > s.new then @gxl + 1 else 0 end as gxl -- "高9序列" , s.new as "4天前收盘价" , t.new - s.new as "收盘价差" from (select t.securitysname , t.tradedate , t.new , @rn := case when @securitycode = t.securitycode then @rn + 1 else 1 end as rn , @securitycode := t.securitycode as securitycode from (select b.securitysname , date_format(d.ppdate, '%Y-%m-%d') as tradedate , a.secinnercode , round(a.new, 2) as new -- 收盘价 , a.securitycode as securitycode from trad_sk_daily_jc a join cdsy_secucode b on b.secinnercode = a.secinnercode join (select ppdate from trad_td_tdate td where td.trademarketcode = '069001001' and td.eisdel = '0' and td.ppdate between date_sub(@tradedate, interval 2 month) and @tradedate order by ppdate desc limit 30) d where b.trademarketcode not in ('069001004001') and b.trademarketcode not in ('069001002005', '069001001003') and b.trademarketcode not in ('069001017') and d.ppdate = a.tradedate and a.eisdel = '0' group by a.securitycode, a.tradedate order by a.securitycode, a.tradedate) t join (select @rn := 0, @securitycode := '') g) t join (select t.securitysname , t.tradedate , t.new , @rn := case when @securitycode = t.securitycode then @rn + 1 else 1 end as rn , @securitycode := t.securitycode as securitycode from (select b.securitysname , date_format(d.ppdate, '%Y-%m-%d') as tradedate , a.secinnercode , round(a.new, 2) as new -- 收盘价 , a.securitycode as securitycode from trad_sk_daily_jc a join cdsy_secucode b on b.secinnercode = a.secinnercode join (select ppdate from trad_td_tdate td where td.trademarketcode = '069001001' and td.eisdel = '0' and td.ppdate between date_sub(@tradedate, interval 2 month) and @tradedate order by ppdate desc limit 34) d where b.trademarketcode not in ('069001004001') and b.trademarketcode not in ('069001002005', '069001001003') and b.trademarketcode not in ('069001017') and d.ppdate = a.tradedate and a.eisdel = '0' group by a.securitycode, a.tradedate order by a.securitycode, a.tradedate) t join (select @rn := 0, @securitycode := '') g) s on s.securitycode = t.securitycode and s.rn = t.rn order by t.securitycode, t.tradedate) x where (x.dxl = 10 or x.gxl = 10) and x.tradedate >= date_sub(@tradedate, interval 1 day) order by dxl, gxl, securitycode; ``` ### Related issues _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
