时间戳

首页 > 解决方案 > VERTICA：如何创建循环

sql - VERTICA：如何创建循环

问题描述

我正在尝试根据用 SAS 编写的示例在 Vertica 中创建一个循环。

我怎样才能做到这一点？

do i = 1 to 24;
    if 3 <= 24 - (intck('MONTH', &startdate, datepart(accountopeneddate))) - i + 1 <= 24 
        then stat[i] = status[24 - (intck('MONTH', &startdate, datepart(accountopeneddate))) - i + 1];
        else stat[i] = '';
end;
run;

标签： sqlloopssasvertica

解决方案

Vertica 是一个普通的 SQL 数据库。SQL 没有循环，这通常是一个优势。此外，SQL 没有数组，没有变量，也没有办法将任何东西分配给任何东西。

您最多可以创建一个包含与数组元素一样多的行的表。

如果你的status[]数组有 24 个元素，你可以去：

\set startdate '''2019-01-01'''                                                                                             

DROP TABLE IF EXISTS show;
CREATE TABLE show AS
WITH
status_tb(i,status) AS (
          SELECT   1,'unrevised draft'
UNION ALL SELECT   2,'1st revised draft'
UNION ALL SELECT   3,'2nd revised draft'
UNION ALL SELECT   4,'3rd revised draft'
UNION ALL SELECT   5,'4th revised draft'
UNION ALL SELECT   6,'5th revised draft'
UNION ALL SELECT   7,'6th revised draft'
UNION ALL SELECT   8,'7th revised draft'
UNION ALL SELECT   9,'8th revised draft'
UNION ALL SELECT  10,'9th revised draft'
UNION ALL SELECT  11,'1st rejected draft'
UNION ALL SELECT  12,'2nd rejected draft'
UNION ALL SELECT  13,'3rd rejected draft'
UNION ALL SELECT  14,'4th rejected draft'
UNION ALL SELECT  15,'5th rejected draft'
UNION ALL SELECT  16,'6th rejected draft'
UNION ALL SELECT  17,'7th rejected draft'
UNION ALL SELECT  18,'8th rejected draft'
UNION ALL SELECT  19,'9th rejected draft'
UNION ALL SELECT  20,'1st approved version'
UNION ALL SELECT  21,'2rd approved version'
UNION ALL SELECT  22,'3th approved version'
UNION ALL SELECT  23,'4th approved version'
UNION ALL SELECT  24,'5th approved version'
)
,
account(accountopeneddate) AS (
SELECT '2019-08-15'::DATE
)
SELECT
    i.i
  , status
  , DATEDIFF(MONTH,:startdate,accountopeneddate) AS mth_btw
FROM account CROSS JOIN (SELECT i FROM status_tb) i
LEFT JOIN status_tb
  ON
    CASE
      WHEN DATEDIFF(MONTH,:startdate,accountopeneddate) - i.i + 1 BETWEEN 3 AND 24
      THEN DATEDIFF(MONTH,:startdate,accountopeneddate) - i.i + 1
      ELSE NULL
    END = status_tb.i
;
SELECT * FROM show ORDER BY 1;
-- out DROP TABLE
-- out Time: First fetch (0 rows): 7.469 ms. All rows formatted: 7.496 ms
-- out CREATE TABLE
-- out Time: First fetch (0 rows): 81.812 ms. All rows formatted: 81.824 ms
-- out  i  |      status       | mth_btw 
-- out ----+-------------------+---------
-- out   1 | 6th revised draft |       7
-- out   2 | 5th revised draft |       7
-- out   3 | 4th revised draft |       7
-- out   4 | 3rd revised draft |       7
-- out   5 | 2nd revised draft |       7
-- out   6 |                   |       7
-- out   7 |                   |       7
-- out   8 |                   |       7
-- out   9 |                   |       7
-- out  10 |                   |       7
-- out  11 |                   |       7
-- out  12 |                   |       7
-- out  13 |                   |       7
-- out  14 |                   |       7
-- out  15 |                   |       7
-- out  16 |                   |       7
-- out  17 |                   |       7
-- out  18 |                   |       7
-- out  19 |                   |       7
-- out  20 |                   |       7
-- out  21 |                   |       7
-- out  22 |                   |       7
-- out  23 |                   |       7
-- out  24 |                   |       7

推荐阅读