SQL实例

Niklaus9084

已于 2023-10-24 18:59:18 修改

阅读量99

点赞数

分类专栏： SQL数据库文章标签： 1024程序员节 sql 数据库

于 2023-10-24 17:27:30 首次发布

本文链接：https://blog.csdn.net/sinat_27990067/article/details/133997228

版权

SQL数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章讨论了使用递归函数在SQL中进行字符串拆分的方法，以及如何在Python和SQL中使用ROW_NUMBER()函数进行行序号计算和连续非零数据的分组。

摘要由CSDN通过智能技术生成

递归拆分字符串

--原始字符串：0;1;2:137-149;3:141-142;4:135-141,5:149-151,146-154;6;7;8#
WITH RECURSIVE c1 (
    timestamp,
    did,
    rs1,
    ss1
)
AS (
    SELECT timestamp,
           did,
           valueStr || ';' AS rs1,
           SUBSTR(valueStr || ';', 1, INSTR(valueStr || ';', ';') - 1) AS ss1
      FROM background_data
    UNION ALL
    SELECT timestamp,
           did,
           SUBSTR(rs1, INSTR(rs1, ';') + 1),
           SUBSTR(rs1, INSTR(rs1, ';') + 1, INSTR(SUBSTR(rs1, INSTR(rs1, ';') + 1) || ';', ';') - 1) 
      FROM c1
     WHERE rs1 <> ''
),
c2 (
    timestamp,
    did,
    rs2,
    ss2
)
AS (
    SELECT timestamp,
           did,
           ss1 || ',' AS rs2,
           SUBSTR(ss1 || ',', 1, INSTR(ss1 || ',', ',') - 1) AS ss2
      FROM c1
    UNION ALL
    SELECT timestamp,
           did,
           SUBSTR(rs2, INSTR(rs2, ',') + 1),
           SUBSTR(rs2, INSTR(rs2, ',') + 1, INSTR(SUBSTR(rs2, INSTR(rs2, ',') + 1) || ',', ',') - 1) 
      FROM c2
     WHERE rs2 <> ''
),
c3 AS (
    SELECT timestamp,
           did,
           SUBSTR(ss2, INSTR(ss2 || ':', ':') + 1) AS [l-r]
      FROM c2
     WHERE rs2 <> ''
),
c4 AS (
    SELECT timestamp,
           did,
           SUBSTR([l-r], 1, INSTR([l-r], '-') - 1) AS [left],
           replace(SUBSTR([l-r], INSTR([l-r], '-') + 1),'#','') AS [right]
      FROM c3
     WHERE [l-r] LIKE '%-%'
     ORDER BY timestamp
)
SELECT *
  FROM c4
 ORDER BY timestamp;

每20个数据计算平均值,python不支持row_number()

WITH t1 AS (
   SELECT value AS [mopLI(mA)],
          timestamp
     FROM serial_log
    WHERE param = 'mopLI(mA)'
),
--为每一行添加序号
t2 AS (
   SELECT row_number() OVER (ORDER BY timestamp) AS rowno,
          timestamp,
          [mopLI(mA)]
     FROM t1
),
--将序号整除20，结果做为分组序号
t3 AS (
   SELECT (rowno - 1) / 20 AS group_row,
          timestamp,
          avg([mopLI(mA)]) AS [mopLI(mA)]
     FROM t2
    GROUP BY group_row
)
SELECT *
 FROM t3;

连续非零数据分组

SELECT id,
       left_mop_pose AS mop_pose,
       left_mop_motor_elec AS mop_motor_elec,
       left_mop_speed AS mop_speed,
       row_number() OVER (ORDER BY id) - row_number() OVER (PARTITION BY left_mop_motor_elec / left_mop_motor_elec ORDER BY id) AS grp
  FROM up_down;

row_number() OVER (ORDER BY id) 按照id排序并添加行序号
PARTITION BY按关键字分组排序
row_number() OVER (PARTITION BY left_mop_motor_elec / left_mop_motor_elec ORDER BY id) 自除值做为分组关键字用于区分0和非0数据
上述两个序号相减，同一个组内两个序号相减是相同的，从而实现分组