我可以在 Redshift 上的存储过程中将两个查询联接在一起吗?

>最初我正在尝试使用一个函数来尝试这个，但无济于事。Redshift 不允许表返回类型或从表中选择数据。我希望也许有一种方法可以使用存储过程来做到这一点，但我不再有太多信心了。我已经为下面列出的代码创建了一个 SP，是否可以将两个查询连接到 SP 自身的两个查询？

尝试做一个功能，不可能发生我已经阅读了亚马逊为 SP 提供的纤薄文档，但没有得到我正在寻找的答案

YTD current year
select 
   year
  , month
  , revenue
  ,transactiondate
  , Flag
from (
select
  extract(year from transactiondate) as year
  , to_char(transactiondate, 'Mon') as month
  , extract(month from transactiondate) as month_number
  , sum(netamount) as revenue
  ,transactiondate
  ,Flag
from
  vw_costs_of_businesss_copy
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD') ->=  concat(to_char(extract(year from '2019-01-01'), 'YYYY'),'01-01') --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(extract(year from to_date('2019-01-01', 'YYYY-MM-DD'))),'01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cas(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as text ),'-01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as Text),'0000'),'-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD')>= to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as Text),'0000'),'-01') , 'YYYY-MM-DD')
group by
  year
  , month
  , month_number
  ,transactiondate
 , Flag
)
order by month_number, year
--YTD PREV YEAR
select 
   year
  , month
  , revenue
  ,transactiondate
  , Flag
from (
select
  extract(year from transactiondate) as year
  , to_char(transactiondate, 'Mon') as month
  , extract(month from transactiondate) as month_number
  , sum(netamount) as revenue
  ,transactiondate
  ,Flag
from
  vw_costs_of_businesss_copy
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD') ->=  concat(to_char(extract(year from '2019-01-01'), 'YYYY'),'01-01') --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(extract(year from to_date('2019-01-01', 'YYYY-MM-DD'))),'01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cas(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as text ),'-01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as Text),'0000'),'-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD')>= to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')-1) as Text),'0000'),'-01') , 'YYYY-MM-DD')
group by
  year
  , month
  , month_number
  ,transactiondate
 , Flag
)
order by month_number, year

它需要列出 YTD 和prev_YTD，以便我可以将其作为参数传递，这可能吗？我意识到我必须为它做一张桌子。我在哪里放置Prev_year、上个月列

我认为您正在寻找UNION ALL的概念来"连接两个查询"。

下面是使用存储过程的示例。有关更多信息，请参阅"Amazon Redshift 中的存储过程概述"。

CREATE PROCEDURE ytd_comparison(
      date_param IN    DATE
    , result_set INOUT REFCURSOR) 
AS $$
BEGIN
    OPEN result_set FOR --YTD
    SELECT year , month , revenue , transactiondate , Flag
    FROM (SELECT EXTRACT(year FROM transactiondate) AS year
               , TO_CHAR(transactiondate, 'Mon') AS month
               , EXTRACT(month FROM transactiondate) AS month_number
               , SUM(netamount) AS revenue
               , transactiondate
               , Flag
          FROM vw_costs_of_businesss_copy
          WHERE TO_DATE(transactiondate, 'YYYY-MM-DD') <= date_param
            AND TO_DATE(transactiondate, 'YYYY-MM-DD') >= DATE_TRUNC('year',date_param)
          GROUP BY year , month , month_number , transactiondate , Flag
          )
    UNION ALL --YTD PREV YEAR
    SELECT year ,month ,revenue ,transactiondate ,Flag
    FROM (SELECT EXTRACT(year FROM transactiondate) AS year
               , TO_CHAR(transactiondate, 'Mon') AS month
               , EXTRACT(month FROM transactiondate) AS month_number
               , SUM(netamount) AS revenue
               , transactiondate
               , Flag
          FROM vw_costs_of_businesss_copy
          WHERE TO_DATE(transactiondate, 'YYYY-MM-DD') <= DATEADD(year, -1, date_param)
            AND TO_DATE(transactiondate, 'YYYY-MM-DD') >= DATE_TRUNC('year', DATEADD(year, -1, date_param))
          GROUP BY year , month , month_number , transactiondate , Flag
          )
    ORDER BY month_number , year
    ;
    RETURN;
END;
$$ LANGUAGE plpgsql;

调用过程时，需要为输出REFCURSOR提供一个名称。必须使用 BEGIN 打开事务以维护游标，然后从REFCURSOR获取输出，然后随END事务一起放弃结果游标。

BEGIN;
CALL ytd_comparison ('2016-07-01'::DATE, 'ytd_results'); 
FETCH ALL FROM ytd_results; 
END;

更新：
或者，您可以仅为前一天创建一个视图。

CREATE OR REPLACE VIEW ytd_comparison AS
SELECT year , month , revenue , transactiondate , Flag
FROM (SELECT EXTRACT(year FROM transactiondate) AS year
           , TO_CHAR(transactiondate, 'Mon') AS month
           , EXTRACT(month FROM transactiondate) AS month_number
           , SUM(netamount) AS revenue
           , transactiondate
           , Flag
      FROM vw_costs_of_businesss_copy
      WHERE TO_DATE(transactiondate, 'YYYY-MM-DD') <= GETDATE() - 1
        AND TO_DATE(transactiondate, 'YYYY-MM-DD') >= DATE_TRUNC('year',GETDATE() - 1)
      GROUP BY year , month , month_number , transactiondate , Flag
      )
UNION ALL --YTD PREV YEAR
SELECT year ,month ,revenue ,transactiondate ,Flag
FROM (SELECT EXTRACT(year FROM transactiondate) AS year
           , TO_CHAR(transactiondate, 'Mon') AS month
           , EXTRACT(month FROM transactiondate) AS month_number
           , SUM(netamount) AS revenue
           , transactiondate
           , Flag
      FROM vw_costs_of_businesss_copy
      WHERE TO_DATE(transactiondate, 'YYYY-MM-DD') <= DATEADD(year, -1, GETDATE() - 1)
        AND TO_DATE(transactiondate, 'YYYY-MM-DD') >= DATE_TRUNC('year', DATEADD(year, -1, GETDATE() - 1))
      GROUP BY year , month , month_number , transactiondate , Flag
      )
ORDER BY month_number , year
;

相关内容

最新更新

热门标签：