yuan
元
xiao
宵
jie
节
今日元宵佳节,吃一碗热乎乎的汤圆
月圆满,人团圆
江城流光溢彩,万家灯火璀璨
如同星河无垠,托起一轮明月
2018年的元宵节,我们曾分享过用 Oracle 语句绘制的团圆图(详情参见:元宵快乐:看SQL大师们用SQL绘制的团圆 ),让我们见识到 SQL 的强大。
如今,三年过去了,国内数据库市场可谓万象更新,国产数据库呈现出百花齐放的喜人态势。在牛年的元宵节,云和恩墨性能优化专家罗海雄老师,以原有 SQL 为基础,用国产数据库 MogDB 的 SQL 语句再次绘制了别样的团圆图,以这种特别的方式为数据库从业人员送上祝福。
在这里分享给大家:
首先是一个团团圆圆五连环
上 SQL!
with
RECURSIVE T as (
select xx.star,xx.x,xx.y from xx
where x = (select min(x) from a)
union all
select xx.star||T.star,xx.x,xx.y from XX
JOIN T ON XX.y=T.y
and XX.x = T.x+1
),
a as (select distinct round(a.x + b.x) x,round(a.y + b.y) y from
(select (sum(x) over(order by n) ) x,
round(sum(y) over(order by n)) y
from (select n, cos(n/30 * 3.1415926)*2 x,
sin(n/30 * 3.1415926) y
from (select row_number() over() - 1 n from pg_attribute limit 30 +30) a1
)a2) a,
(select n, (sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos( m /3 * 3.1415926) * 2 * 15 x,
sin( m /3 * 3.1415926)* 15 y
from (select case when row_number() over() <= 2 then 3
when row_number() over() = 3 then -2 else -6 end m, row_number() over() - 1 n
from pg_attribute limit 5)b1)b2) b
),
xx as (
select point star,x,y
from (select b.y, b.x, decode(a.x, null, ' ', '*') point
from a,
(select *
from (select row_number() over() - 1 + (select min(x) from a) x
from pg_attribute
limit (select max(x) - min(x) + 1 from a)),
(select row_number() over() - 1 + (select min(y) from a) y
from pg_attribute
limit (select max(y) - min(y) + 1 from a) )b5 ) b
where a.x(+) = b.x
and a.y(+) = b.y)
--where x = (select max(x) from a)
)
select star from T
where x = (select max(x) from a)
这里有几个改写要点:
1. MogDB不支持直接的rownum写法,但是可以row_number() over()的形式来写。
2. 同样,如果rownum不是出现在select中,而是出现在where条件中,就不能用row_number() over()来代替,这时候,可以使用limit N 来代替 rownum <= N 。
3. MogDB不支持Oracle的connect by/start with的树状查询写法,但可以用with RECURSIVE T(select * from xx union all select .. from xx join T on T.?? = x.?? )的写法来改写。
4. 原SQL中用到了dba_objects来产生大量行,MogDB可以用类似的字典表,如pg_attribute来替换。
接下来这一段SQL
输出一个五角星☆
with a as ( select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n / 20) * (1-1/5) * 3.1415926) * 2 x,
sin(trunc(n / 20) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from pg_attribute limit 20 * 5))
)
SELECT replace(lpad(sum(star),40,'0'),'0',' ') from
(select rpad('4',x,'0') as star,x,y
FROM a)
GROUP BY y
ORDER BY y;
大家看看,是不是感觉很有趣?欢迎大家用自己常用的数据库绘制多样的图案,为多(ku)彩(zao)的工作加点料。墨天轮平台 modb.pro,汇聚40万数据库从业者,期待看到大家的作品~~
云和恩墨携MogDB,祝大家元宵节快乐!
相关阅读: