首届SQL挑战赛获奖名单公布！

墨天轮基于SQLRUN 工具推出【第一期SQL挑战赛】，多名技术爱好者以技术实力与多维视角呈现出了精彩的作品，在此展示大赛获奖名单。

最佳SQL：DarkAthena

该作者最先提交作品，且运用了两种方式来展现，让我们一起欣赏作品👇

作品展示一：递归方式

with cte(seqno,amount,tot_amount) as (
  select t.seqno,t.amount,amount tot_amount from test_tab t where t.seqno=1
  union all
  select t.seqno,t.amount,case when cte.tot_amount>0 then t.amount else t.amount+cte.tot_amount end 
  from test_tab t ,cte where t.seqno=cte.seqno+1
)
select * from cte

作品展示二：Model子句

select * from test_tab
model return updated rows
dimension by (seqno)
measures (amount,amount tot_amount)
rules (
  tot_amount[any] order by seqno = (case when tot_amount[cv()-1]>0 then amount[cv()] else  nvl(amount[cv()],0)+nvl(tot_amount[cv()-1],0) end )
);

挑战勇者

除了最佳SQL奖以外，社区本次还设置了10个挑战勇者奖，按照提交顺序对合格作品进行1000*0.8的阶梯式奖励。

🔔所有获奖选手的墨值奖励将于12月19日下午18点前发送至个人帐户

部分优秀作品展示

WITH RECURSIVE cte ( seqno, amount, total_amount ) AS (
	SELECT T
		.seqno,
		T.amount,
		amount  total_amount 
	FROM
		test_tab T 
	WHERE
		T.seqno = 1 UNION ALL
	SELECT T
		.seqno,
		T.amount,
	CASE
			
			WHEN cte. total_amount > 0 THEN
			T.amount ELSE T.amount + cte. total_amount 
		END 
		FROM
			test_tab T,
			cte 
		WHERE
			T.seqno = cte.seqno + 1 
		) SELECT
		* 
FROM
	cte

WITH T1(SEQNO,AMOUNT,TOT_AMOUNT) AS (
  SELECT T.SEQNO,T.AMOUNT,AMOUNT TOT_AMOUNT FROM TEST_TAB T WHERE T.SEQNO<2
  UNION ALL
  SELECT T.SEQNO,T.AMOUNT,CASE WHEN T1.TOT_AMOUNT>0 THEN T.AMOUNT ELSE T.AMOUNT+T1.TOT_AMOUNT END TOT_AMOUNT
  FROM TEST_TAB T ,T1 WHERE T.SEQNO=T1.SEQNO+1
)
SELECT * FROM T1

SELECT *
  FROM TEST_TAB MODEL RETURN UPDATED ROWS DIMENSION BY(SEQNO) MEASURES(AMOUNT, AMOUNT TOT_AMOUNT) RULES(TOT_AMOUNT [ ANY ] ORDER BY SEQNO = (CASE
         WHEN TOT_AMOUNT [CV() - 1] IS NULL THEN  AMOUNT [ CV() ]
         WHEN TOT_AMOUNT [CV() - 1] > 0 THEN AMOUNT [ CV() ]
         ELSE AMOUNT [ CV() ] +TOT_AMOUNT [CV() - 1 ] END));

with cte(seqno,amount,tot_amount) as 
  (select t.seqno,t.amount,amount tot_amount from test_tab t where t.seqno=1
  union all
  select t.seqno,t.amount,decode(sign(cte.tot_amount),1,t.amount,-1,t.amount+cte.tot_amount)
  from test_tab t ,cte where t.seqno=cte.seqno+1)
select * from cte

根据给定数据，按序号seqno累计，上月累计金额＝amount＋上月累计金额，但是如果上月累计金额是正值，就不计入下个月，重新累计。

/*原始数据*/
drop table test_tab purge;

create table test_tab as
(
select 1 as seqno,50 as amount from dual
union all
select 2 as seqno,-100 as amount from dual
union all
select 3 as seqno,20 as amount from dual
union all
select 4 as seqno,50 as amount from dual
union all
select 5 as seqno,-20 as amount from dual
union all
select 6 as seqno,-30 as amount from dual
union all
select 7 as seqno,40 as amount from dual
union all
select 8 as seqno,100 as amount from dual
union all
select 9 as seqno,20 as amount from dual
union all
select 10 as seqno,-70 as amount from dual
union all
select 11 as seqno,40 as amount from dual
);

要求
按序号seqno累计
上月累计金额＝amount＋上月累计金额
但是如果上月累计金额是正值，就不计入下个月，重新累计，结果如下：

SEQNO  AMOUNT      TOT_AMOUNT
1      50          50
2      -100        -100
3      20          -80
4      50          -30
5      -20         -50
6      -30         -80
7      40          -40
8      100         60
9      20          20
10     -70         -70
11     40          -30

首期【SQL挑战赛】在此落下帷幕，墨天轮社区致敬每位热爱技术、乐于展现的墨友！目前SQLRUN 工具已支持Oracle、PostgreSQL、openGauss以及MogDB等多个数据库，在未来我们将囊括更多的版本，期望它能够助你增效，也欢迎大家向我们反馈建议，携手走向更好！