计算连续签到天数

newkid

楼上的SQL技巧很熟练了，要不要写个大数据量的测试用例比较一下几种方法的性能？

chengccy2010

newkid 发表于 2022-8-29 21:39
楼上的SQL技巧很熟练了，要不要写个大数据量的测试用例比较一下几种方法的性能？

大概可以猜得到，如果表有RN列且是主键，肯定是cte快，否则的话model快。 没有12c以上的环境，测试不了新语法。现在都信创了，更不敢用新语法了，model都不能用了。

newkid

用国产数据库就是信创吗？在ORACLE优化器面前，它们都是弱鸡。这本来是SQL人大显身手的时刻，比如我本来没用过PG, 但是看它的执行计划也能猜个八九不离十，还能指导其他项目组改写来提高性能。但是现在有些公司却因此推出一些奇怪的开发规范，比如表连接不能超过三张之类，实在令人无语！

theshy

/*
本人菜鸟一枚想到这样一个办法：可以把截止当前行 0 出现的次数作为分组的依据，然后组内进行累加
代码如下：  其中的group_flag就是截止当前行sign_In=0出现的次数
*/
WITH T AS (
    SELECT '2022-01-01' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-02' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-03' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-04' AS DT, 0 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-05' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-06' AS DT, 0 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-07' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-08' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-09' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-10' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-11' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-12' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-13' AS DT, 0 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-14' AS DT, 0 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-15' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-16' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-17' AS DT, 1 AS SIGN_IN FROM DUAL UNION
    SELECT '2022-01-18' AS DT, 1 AS SIGN_IN FROM DUAL
)
,T1 AS (SELECT T.*,COUNT(DECODE(T.SIGN_IN,0,1)) OVER (ORDER BY DT) GROUP_FLAG FROM T)
SELECT T1.DT,T1.SIGN_IN,SUM(SIGN_IN) OVER (PARTITION BY GROUP_FLAG ORDER BY T1.DT) 连续签到  FROM T1;

newkid

theshy 发表于 2022-9-10 00:21
/*本人菜鸟一枚想到这样一个办法：可以把截止当前行 0 出现的次数作为分组的依据，然后组内进行累加 代码如 ...

这个方法可以的，我以前也用过：
http://www.itpub.net/thread-1223937-1-1.html

theshy

newkid 发表于 2022-9-10 04:11
这个方法可以的，我以前也用过：http://www.itpub.net/thread-1223937-1-1.html

苏神回我帖子了，好开心啊

newkid

theshy 发表于 2022-9-11 12:01
苏神回我帖子了，好开心啊

希望以后多来陪广场舞大爷玩。

chengccy2010

newkid 发表于 2022-9-8 22:23
用国产数据库就是信创吗？在ORACLE优化器面前，它们都是弱鸡。这本来是SQL人大显身手的时刻，比如我本来没 ...

怕被制裁，国内是大势所趋了。 都在上信创