雪花 – 根据特定条件从多个表中计算天数

huangapple
go评论96阅读模式
英文:

Snowflake - Calculating days from multiple tables based on specific condition

问题

有人能帮忙编写雪花查询以获得正确结果。我正在处理雪花并有3个表：ZBK、ZLF和ZT05

TAG（整数）是月份的日期。它只用于与日期（inv_dt）进行比较，可以为00
MONA（整数）是月份数，即01表示1个月...
FAEL是月份的日期，即20表示月份的第20天。

TAG1是具有附加天数的列，需要在最终求和计算中添加，不带任何条件。

如果inv_dt小于TAG，则计算天数时应考虑供应商的特定条款，并根据该特定标签行应用计算规则。

例如-对于第一行，Tag是16，而day(inv_dt)是7，小于Tag，则在计算天数时，应取Fael的值，即20，mona为00，并从2月7日到2月20日计算天数，即13天。

对于第二行，Tag是25，而day(inv_dt)是17，小于25但大于同一供应商和条款组的16，则计算应该是Fael即20，mona为01，并从3月17日到4月20日计算天数，即34天（3月14日+4月20日的14天）。

  1. Inv_Dt    Vendor  |  Vendor   Term |  TAG  FAEL MONA Term  TAG1
  2. 7/02/23   100428  |  100428   AA5  |  16   20    00   AA5   00
  3. 17/3/23   100428  |  100429   CA2  |  25   20    01   AA5   07
  4. 26/3/23   100428  |                |  31   10    02   AA5   15
  5. 22/4/23   100429  |                |  00   07    02   CA2   5
  7. 期望输出（sum_days列）- 仅作参考的列表列。
  8. inv_dt     term    tag tag1    fael  mona    sum_days  list
  9. 2023-02-07    AA5     16    00      20      00    13      13
  10. 2023-03-17    AA5     25    07      20      01    41      14+20   + 07(tag1)
  11. 2023-03-26    AA5     31    15      10      02    60      5+30+10 + 15(tag1)
  12. 2023-04-22    CA2     00    05      7        02    51      8+31+7  + 05(tag1)

我有一个SQL的样本查询如下

  1. 与连接一样(
  2. 选择ZBK.Inv_Dt，ZLF.TERM
  3. 从ZBK
  4. 联接ZLF on ZBK.Vendor = ZLF.Vendor)
  5. 选择Inv_Dt，TERM，TAG，FAEL，MONA，sum(FAEL) sum_days，
  6.        string_agg(FAEL，'+') 在n的排序下（组内）列表
  7. 从(
  8.   选择Inv_Dt，a.TERM，TAG，FAEL，MONA，n，
  9.        案例
  10.        当MONA = 0并且FAEL > day(Inv_Dt)时，FAEL - day(Inv_Dt)
  11.        当n = 0时，datediff(day，Inv_Dt，last_day(Inv_Dt))
  12.        当n = MONA时，FAEL
  13.        其他时候，day(last_day(dateadd(month，n，Inv_Dt)))
  14.        结束天数
  15.   从连接a
  16.   十字应用(选择top 1 * from ZT05 b
  17.                     其中b.TERM = a.TERM and day(Inv_Dt) < TAG顺序为TAG) b，
  18.   十字应用(选择n from (values (0)，(1)，(2)，(3)，(4)，(5)) v(n) 其中n <= MONA) v) t
  19. 组按Inv_Dt，TERM，TAG，FAEL，MONA

我正在寻找一种雪花查询。在对此查询进行修改后，运行该查询时出现错误，错误消息为“SQL编译错误：不支持的子查询类型无法评估”。

谢谢

英文:

Can anyone help in snowflake query to get the correct result. I am working on snowflake and have 3 tables as ZBK, ZLF and ZT05

TAG(int) is the day of the month. It is only to compare with day(inv_dt) It can be 00
MONA(int) is the No. of Months i.e. 01 means 1 Month...
FAEL is the day of the month i.e 20 means 20th day of the month.

TAG1 is the column which has additional days which needs to be added in the final sum calculation without any condition.

If the inv_dt is less then TAG then the no. of days calculation should consider specific Term for a vendor and apply the rule of calculation as per that specific tag row.

example - for 1st row, Tag is 16 and day(inv_dt) is 7 which is less then Tag then while calculating no. of days it should take value of Fael i.e. 20 and mona as 00 and count no. of days from 7th Feb to 20th Feb which is 13.

For 2nd row, Tag is 25 and day(inv_dt) is 17 which is less then 25 but greater then 16 of the same vendor and term group. then calculation should be Fael i.e. 20 and mona as 01 and count no. of days from 17th March to 20th April which is 34 (14 day of March + 20 Days of April)

  1. Table ZBK              Table ZLF        ZT05
  2. Inv_Dt    Vendor  |  Vendor   Term |  TAG  FAEL MONA Term  TAG1
  3. 7/02/23   100428  |  100428   AA5  |  16   20    00   AA5   00
  4. 17/3/23   100428  |  100429   CA2  |  25   20    01   AA5   07
  5. 26/3/23   100428  |                |  31   10    02   AA5   15
  6. 22/4/23   100429  |                |  00   07    02   CA2   5
  8. Desired Output (sum_days column) - List column just for reference.
  9. inv_dt	   term	 tag tag1	fael  mona	sum_days  list
  10. 2023-02-07	AA5	 16	  00     20	   00	  13	  13
  11. 2023-03-17	AA5	 25	  07     20	   01	  41	  14+20   + 07(tag1)
  12. 2023-03-26	AA5	 31	  15     10	   02	  60	  5+30+10 + 15(tag1) 
  13. 2023-04-22	CA2	 00	  05      7	   02	  51	  8+31+7  + 05(tag1)

I have a sample query on SQL as below

  1. with joined as(
  2. select ZBK.Inv_Dt, ZLF.TERM 
  3.   from ZBK
  4.     join ZLF on ZBK.Vendor = ZLF.Vendor)
  5. select Inv_Dt, TERM, TAG, FAEL, MONA, sum(FAEL) sum_days,
  6.        string_agg(FAEL, &#39;+&#39;) within group (order by n) list
  7. from(
  8.   select Inv_Dt, a.TERM, TAG, FAEL, MONA,n, 
  9.        case 
  10.        when MONA = 0 and FAEL &gt; day(Inv_Dt) then FAEL - day(Inv_Dt)
  11.        when n = 0 then datediff(day, Inv_Dt, last_day(Inv_Dt)) 
  12.        when n = MONA then FAEL 
  13.        else day(last_day(dateadd(month, n, Inv_Dt)))
  14.        end days
  15.   from joined a
  16.   cross apply(select top 1 * from ZT05 b
  17.                     where b.TERM = a.TERM and day(Inv_Dt) &lt; TAG order by TAG) b,
  18.   cross apply(select n from (values (0),(1),(2),(3),(4),(5)) v(n) where n &lt;= MONA) v) t
  19. group by Inv_Dt, TERM, TAG, FAEL, MONA

I am looking for a query on snowflake.
When I am running this query after modification of string_agg to listagg() and cross apply to lateral in snowflake. I am getting error of SQL compilation error: Unsupported subquery type cannot be evaluated
Thank you

答案1

得分: 1

以下是已翻译的代码部分：

  1. 这将给您所需的信息：
  3.     选择 ZBK.INV_DT
  4.     ,ZLF.TERM
  5.     ,T1.TAG, T1.FAEL, T1.MONA
  6.     ,日期差('天',ZBK.INV_DT, 
  7.         添加月份(
  8.                     日期_从_部分(年(ZBK.INV_DT), 月(ZBK.INV_DT), T1.FAEL)
  9.                     ,T1.MONA
  10.                   )
  11.              ) "总天数"
  12.     从 ZBK
  13.     内部连接 ZLF ON ZBK.VENDOR = ZLF.VENDOR
  14.     内部连接 ZT05 T1 ON ZLF.TERM = T1.TERM AND T1.TAG = 
  15.         (选择 MIN(T2.TAG) 从 ZT05 T2 WHERE ZLF.TERM = T2.TERM AND T2.TAG > 天(ZBK.INV_DT) )
  16.     按 ZBK.INV_DT 排序
  17.     ;
英文:

This should give you what you want:

  1. SELECT ZBK.INV_DT
  2. ,ZLF.TERM
  3. ,T1.TAG, T1.FAEL, T1.MONA
  4. ,DATEDIFF(&#39;DAYS&#39;,ZBK.INV_DT, 
  5.     ADD_MONTHS(
  6.                 DATE_FROM_PARTS(YEAR(ZBK.INV_DT), MONTH(ZBK.INV_DT), T1.FAEL)
  7.                 ,T1.MONA
  8.               )
  9.          ) &quot;SUM_DAYS&quot;
  10. FROM ZBK
  11. INNER JOIN ZLF ON ZBK.VENDOR = ZLF.VENDOR
  12. INNER JOIN ZT05 T1 ON ZLF.TERM = T1.TERM AND T1.TAG = 
  13.     (SELECT MIN(T2.TAG) FROM ZT05 T2 WHERE ZLF.TERM = T2.TERM AND T2.TAG &gt; DAY(ZBK.INV_DT) )
  14. ORDER BY ZBK.INV_DT
  15. ;

I've assumed that the tag value has to be greater than the invoice day but if the rule is <= then you'll need to adjust the "T2.TAG > DAY(ZBK.INV_DT)"

huangapple
  • 本文由 发表于 2023年6月26日 20:22:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/76556654.html
  • snowflake-cloud-data-platform
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定