背景

公司内一套自动化平台
我想能够早上到了公司之后,
看看晚上11掉到早上八点运行了多少个自动化任务.
想着可以写一个SQL来实现结果.
所以想着处理一下. 能够通过一个SQL插叙出来结果

复制

创建view

CREATE 
  OR REPLACE VIEW ui_case_Before_8am AS SELECT
  case1.RESULT,
  task.app_url,
  case1.update_time 
FROM
  ui_run_case case1
  INNER JOIN ui_task task ON case1.task_id = task.task_id 
WHERE
  case1.create_time > CURRENT_DATE :: TIMESTAMP - INTERVAL '12 H' 
  AND case1.update_time < CURRENT_DATE :: TIMESTAMP + INTERVAL '8 H'
  AND task.task_name like 'myapp%';
CREATE 
  OR REPLACE VIEW ui_case_Before_8am_1before AS SELECT
  case1.RESULT,
  task.app_url,
  case1.update_time 
FROM
  ui_run_case case1
  INNER JOIN ui_task task ON case1.task_id = task.task_id 
WHERE
  case1.create_time > CURRENT_DATE :: TIMESTAMP - INTERVAL '36 H' 
  AND case1.update_time < CURRENT_DATE :: TIMESTAMP - INTERVAL '16 H'
  AND task.task_name like 'myapp%';
CREATE 
  OR REPLACE VIEW ui_case_Before_8am_2before AS SELECT
  case1.RESULT,
  task.app_url,
  case1.update_time 
FROM
  ui_run_case case1
  INNER JOIN ui_task task ON case1.task_id = task.task_id 
WHERE
  case1.create_time > CURRENT_DATE :: TIMESTAMP - INTERVAL '60 H' 
  AND case1.update_time < CURRENT_DATE :: TIMESTAMP - INTERVAL '40 H'
  AND task.task_name like 'myapp%';
CREATE 
  OR REPLACE VIEW ui_case_Before_8am_7before AS SELECT
  case1.RESULT,
  task.app_url,
  case1.update_time 
FROM
  ui_run_case case1
  INNER JOIN ui_task task ON case1.task_id = task.task_id 
WHERE
  case1.create_time > CURRENT_DATE :: TIMESTAMP - INTERVAL '180 H' 
  AND case1.update_time < CURRENT_DATE :: TIMESTAMP - INTERVAL '160 H'
  AND task.task_name like 'myapp%';

复制

大模型帮助

https://tongyi.aliyun.com/
四个视图  每个视图有两列,  其中一列是url 另外一列合计值,
一个SQL将四个视图的count链接起来. 分列展示每个视图的合计值 
最左侧一列为url 使用pg数据库的语法
最后一行进行合计

复制

给出SQL

SELECT
  * 
FROM
  (
    WITH combined AS (
    SELECT COALESCE
      ( b2.app_url, b1.app_url, b0.app_url, b7.app_url ) AS app_url,
      COALESCE ( b2.cnt, 0 ) AS Before_8am_2before_count,
      COALESCE ( b1.cnt, 0 ) AS Before_8am_1before_count,
      COALESCE ( b0.cnt, 0 ) AS Before_8am_count,
      COALESCE ( b7.cnt, 0 ) AS Before_8am_7before_count 
    FROM
      ( SELECT app_url, COUNT ( 1 ) AS cnt FROM ui_case_Before_8am_2before GROUP BY app_url ) b2
      FULL OUTER JOIN ( SELECT app_url, COUNT ( 1 ) AS cnt FROM ui_case_Before_8am_1before GROUP BY app_url ) b1 ON b2.app_url = b1.app_url
      FULL OUTER JOIN ( SELECT app_url, COUNT ( 1 ) AS cnt FROM ui_case_Before_8am GROUP BY app_url ) b0 ON COALESCE ( b2.app_url, b1.app_url ) = b0.app_url
      FULL OUTER JOIN ( SELECT app_url, COUNT ( 1 ) AS cnt FROM ui_case_Before_8am_7before GROUP BY app_url ) b7 ON COALESCE ( b2.app_url, b1.app_url, b0.app_url ) = b7.app_url 
    ) SELECT
    * 
  FROM
    combined UNION ALL
  SELECT
    'Total' AS app_url,
    SUM ( Before_8am_2before_count ),
    SUM ( Before_8am_1before_count ),
    SUM ( Before_8am_count ),
    SUM ( Before_8am_7before_count ) 
  FROM
    combined 
  ) test 
ORDER BY
CASE
    
    WHEN test.app_url = 'Total' THEN
    2 ELSE 1 
  END,
  Before_8am_2before_count + Before_8am_1before_count + Before_8am_count + Before_8am_7before_count DESC;

复制

最简单的处理

create view  auto as "上一章节的SQL"
这样 select * from auto 就可以了. 

复制