Snowflake SQL – 一对多连接的求和

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

Snowflake SQL - Sum with one-to-many joins

问题

我试图编写一个查询，以显示订单上的项目何时被输入或“键入”，以及该项目何时被开具发票。在某些情况下，一个项目可能会有多个开票日期。由于存在多个开票日期，我得到了SALES_ORDER_ITEM_QUANTITY和KEYED_GSAR字段的重复结果。

以下是我尝试使用的查询。它已经针对一个订单进行了筛选，以说明我遇到的问题。

  1. vwith sales_orders as (
  2.     select 
  3.         O.SALES_ORDER_ITEM_CREATE_DATE,
  4.         O.SALES_ORDER_ID,
  5.         O.MATERIAL_ID,
  6.         O.SALES_ORDER_ITEM_ID,
  7.        sum(distinct o.gsar) as keyed_sales,
  8.        sum(distinct o.sales_order_item_quantity) as SALES_ORDER_ITEM_QTY
  9.     from EDP.RPT_SALES.SALES_ORDERS O
  10.     where O.FISCAL_YR_ID IN (2022,2023)
  11.         AND O.WW_CHANNEL IN ('RENOVATION','INSTALLATION')
  12.         and o.sales_order_id = '0306231843'
  13.         GROUP BY 
  14.         O.SALES_ORDER_ITEM_CREATE_DATE,
  15.         O.SALES_ORDER_ID,
  16.         O.MATERIAL_ID,
  17.         O.SALES_ORDER_ITEM_ID
  18. )
  19. ,billing as (
  20.     select 
  21.         B.BILLING_DOC_DATE,
  22.         B.SALES_DOC_ID,
  23.         B.MATERIAL_ID,
  24.         B.SALES_DOC_ITEM_ID,
  25.         sum(B.BILL_QTY) as BILL_QTY,
  26.         sum(B.GSAR) as GSAR
  27.     from EDP.RPT_SALES.BILLING B
  28.     where b.fiscal_yr_id IN (2022,2023)
  29.         AND b.WW_CHANNEL IN ('RENOVATION','INSTALLATION')
  30.         group by 
  31.         B.BILLING_DOC_DATE,
  32.         B.SALES_DOC_ID,
  33.         B.MATERIAL_ID,
  34.         B.SALES_DOC_ITEM_ID
  35. )
  36. SELECT
  37. O.SALES_ORDER_ITEM_CREATE_DATE,
  38. LTRIM(O.SALES_ORDER_ID,'0') SALES_ORDER_ID,
  39. LTRIM(O.MATERIAL_ID,'0') MATERIAL_ID,
  40. O.SALES_ORDER_ITEM_ID,
  41. O.SALES_ORDER_ITEM_QTY,
  42. KEYED_SALES,
  43. B.BILLING_DOC_DATE,
  44. B.BILL_QTY,
  45. B.GSAR BILLED_SALES
  46. FROM sales_orders O
  47. LEFT JOIN billing B ON O.SALES_ORDER_ID = B.SALES_DOC_ID AND O.MATERIAL_ID = B.MATERIAL_ID AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID

这个订单上有一个材料，编号为128470。订购数量为40，价值$7,783.60。这个材料在4个不同的日期开具了发票，如BILLING_DOC_DATE列所示。SALES_ORDER_ITEM_QUANTITY和KEYED_SALES的值被重复出现了4次。以下是我得到的结果：

SALES_ORDER_ITEM_CREATE_DATE SALES_ORDER_ID MATERIAL_ID SALES_ORDER_ITEM_ID SALES_ORDER_ITEM_QTY KEYED_SALES BILLING_DOC_DATE BILL_QTY BILLED_SALES
2023-03-31 306231843 128470 20 40 7783.60 2023-03-31 31 6032.29
2023-03-31 306231843 128470 20 40 7783.60 2023-03-23 5 972.95
2023-03-31 306231843 128470 20 40 7783.60 2023-04-05 3 58.77
2023-03-31 306231843 128470 20 40 7783.60 2023-03-29 1 194.59

以下是期望的结果：

SALES_ORDER_ITEM_CREATE_DATE SALES_ORDER_ID MATERIAL_ID SALES_ORDER_ITEM_ID SALES_ORDER_ITEM_QTY KEYED_SALES BILLING_DOC_DATE BILL_QTY BILLED_SALES
2023-03-31 306231843 128470 20 40 7783.60 2023-03-31 31 6032.29
2023-03-31 306231843 128470 20 0 0 2023-03-23 5 972.95
2023-03-31 306231843 128470 20 0 0 2023-04-05 3 58.77
2023-03-31 306231843 128470 20 0 0 2023-03-29 1 194.59
英文:

I am trying to write a query that shows when an item on an order was entered or "keyed" in, and then when that item was billed. In certain cases, one item will have multiple billing dates. Because of there being multiple billing dates, I am getting duplicate results of the SALES_ORDER_ITEM_QUANTITY AND KEYED_GSAR fields.

Below is the query that I am trying to use. It is filtered on one order to illustrate the problem I am having.

  1. vwith sales_orders as (
  2.     select 
  3.         O.SALES_ORDER_ITEM_CREATE_DATE,
  4.         O.SALES_ORDER_ID,
  5.         O.MATERIAL_ID,
  6.         O.SALES_ORDER_ITEM_ID,
  7.        sum(distinct o.gsar) as keyed_sales,
  8.        sum(distinct o.sales_order_item_quantity) as SALES_ORDER_ITEM_QTY
  9.     from EDP.RPT_SALES.SALES_ORDERS O
  10.     where O.FISCAL_YR_ID IN (2022,2023)
  11.         AND O.WW_CHANNEL IN ('RENOVATION','INSTALLATION')
  12.         and o.sales_order_id = '0306231843'
  14.         GROUP BY 
  15.         O.SALES_ORDER_ITEM_CREATE_DATE,
  16.         O.SALES_ORDER_ID,
  17.         O.MATERIAL_ID,
  18.         O.SALES_ORDER_ITEM_ID
  19. )
  21. ,billing as (
  22.     select 
  23.         B.BILLING_DOC_DATE,
  24.         B.SALES_DOC_ID,
  25.         B.MATERIAL_ID,
  26.         B.SALES_DOC_ITEM_ID,
  27.         sum(B.BILL_QTY) as BILL_QTY,
  28.         sum(B.GSAR) as GSAR
  29.     from EDP.RPT_SALES.BILLING B
  30.     where b.fiscal_yr_id IN (2022,2023)
  31.         AND b.WW_CHANNEL IN ('RENOVATION','INSTALLATION')
  33.         group by 
  34.         B.BILLING_DOC_DATE,
  35.         B.SALES_DOC_ID,
  36.         B.MATERIAL_ID,
  37.         B.SALES_DOC_ITEM_ID
  38. )
  40. SELECT
  42. O.SALES_ORDER_ITEM_CREATE_DATE,
  43. LTRIM(O.SALES_ORDER_ID,'0') SALES_ORDER_ID,
  44. LTRIM(O.MATERIAL_ID,'0') MATERIAL_ID,
  45. O.SALES_ORDER_ITEM_ID,
  46. O.SALES_ORDER_ITEM_QTY,
  47. KEYED_SALES,
  48. B.BILLING_DOC_DATE,
  49. B.BILL_QTY,
  50. B.GSAR BILLED_SALES
  53. FROM sales_orders O
  54. LEFT JOIN billing B ON O.SALES_ORDER_ID = B.SALES_DOC_ID AND O.MATERIAL_ID = B.MATERIAL_ID AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID

This order has one material on it, 128470. There was a quantity of 40 ordered for a value of $7,783.60. This material was billed on 4 separate dates, as seen in the BILLING_DOC_DATE column. The SALES_ORDER_ITEM_QUANTITY and KEYED_SALES values are getting duplicated 4 times. Below are the results I am getting:

SALES_ORDER_ITEM_CREATE_DATE SALES_ORDER_ID MATERIAL_ID SALES_ORDER_ITEM_ID SALES_ORDER_ITEM_QTY KEYED_SALES BILLING_DOC_DATE BILL_QTY BILLED_SALES
2023-03-31 306231843 128470 20 40 7783.60 2023-03-31 31 6032.29
2023-03-31 306231843 128470 20 40 7783.60 2023-03-23 5 972.95
2023-03-31 306231843 128470 20 40 7783.60 2023-04-05 3 58.77
2023-03-31 306231843 128470 20 40 7783.60 2023-03-29 1 194.59

Below are the desired results

SALES_ORDER_ITEM_CREATE_DATE SALES_ORDER_ID MATERIAL_ID SALES_ORDER_ITEM_ID SALES_ORDER_ITEM_QTY KEYED_SALES BILLING_DOC_DATE BILL_QTY BILLED_SALES
2023-03-31 306231843 128470 20 40 7783.60 2023-03-31 31 6032.29
2023-03-31 306231843 128470 20 0 0 2023-03-23 5 972.95
2023-03-31 306231843 128470 20 0 0 2023-04-05 3 58.77
2023-03-31 306231843 128470 20 0 0 2023-03-29 1 194.59

答案1

得分: 0

我建议在计费数据中添加一个“行号”，以区分要输出销售数据的行，以避免重复。同时，我建议您除非有合法的理由要省略一些数据，否则不要使用 sum(distinct ...)。考虑到您正在从单个表中汇总销售信息，似乎非常不可能需要使用 sum(distinct ...)。即，如果一个订单有2个数量为20的条目，那么总订单数量应该是40，不是吗？

  1. vwith sales_orders AS (
  2.         SELECT O.SALES_ORDER_ITEM_CREATE_DATE
  3.             , O.SALES_ORDER_ID
  4.             , O.MATERIAL_ID
  5.             , O.SALES_ORDER_ITEM_ID
  6.             , sum(o.gsar) AS keyed_sales
  7.             , sum(o.sales_order_item_quantity) AS SALES_ORDER_ITEM_QTY
  8.         FROM EDP.RPT_SALES.SALES_ORDERS O
  9.         WHERE O.FISCAL_YR_ID IN (2022, 2023) AND O.WW_CHANNEL IN ('RENOVATION', 'INSTALLATION') AND o.sales_order_id = '0306231843'
  10.         GROUP BY O.SALES_ORDER_ITEM_CREATE_DATE
  11.             , O.SALES_ORDER_ID
  12.             , O.MATERIAL_ID
  13.             , O.SALES_ORDER_ITEM_ID
  14.         )
  15.     , billing AS (
  16.         SELECT B.BILLING_DOC_DATE
  17.             , B.SALES_DOC_ID
  18.             , B.MATERIAL_ID
  19.             , B.SALES_DOC_ITEM_ID
  20.             , sum(B.BILL_QTY) AS BILL_QTY
  21.             , sum(B.GSAR) AS GSAR
  22.             , row_number() OVER (
  23.                 PARTITION BY B.SALES_DOC_ID
  24.                 , B.MATERIAL_ID
  25.                 , B.SALES_DOC_ITEM_ID ORDER BY B.BILLING_DOC_DATE
  26.                 ) AS rn
  27.         FROM EDP.RPT_SALES.BILLING B
  28.         WHERE b.fiscal_yr_id IN (2022, 2023) AND b.WW_CHANNEL IN ('RENOVATION', 'INSTALLATION')
  29.         GROUP BY B.BILLING_DOC_DATE
  30.             , B.SALES_DOC_ID
  31.             , B.MATERIAL_ID
  32.             , B.SALES_DOC_ITEM_ID
  33.         )
  35. SELECT O.SALES_ORDER_ITEM_CREATE_DATE
  36.     , LTRIM(O.SALES_ORDER_ID, '0') SALES_ORDER_ID
  37.     , LTRIM(O.MATERIAL_ID, '0') MATERIAL_ID
  38.     , O.SALES_ORDER_ITEM_ID
  39.     , CASE WHEN b.rn = 1 THEN O.SALES_ORDER_ITEM_QTY ELSE 0 END AS SALES_ORDER_ITEM_QTY
  40.     , CASE WHEN b.rn = 1 THEN O.KEYED_SALES ELSE 0 END AS KEYED_SALES
  41.     , B.BILLING_DOC_DATE
  42.     , B.BILL_QTY
  43.     , B.GSAR BILLED_SALES
  44. FROM sales_orders O
  45. LEFT JOIN billing B ON O.SALES_ORDER_ID = B.SALES_DOC_ID AND O.MATERIAL_ID = B.MATERIAL_ID AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID
英文:

I suggest you need a "row number" in the billing data to distinguish which row to output the sales data so that you can avoid repeats. Whilst here, I also suggest you don't use sum(distinct ...) unless you have a legitimate reason to omit some data. Given you are summing sales info from a single table it seems very unlikely that you need sum(distinct ...). i.e. If an order had 2 entries of qty = 20 then the total order qty = 40 isn't it?

  1. vwith sales_orders AS (
  2.         SELECT O.SALES_ORDER_ITEM_CREATE_DATE
  3.             , O.SALES_ORDER_ID
  4.             , O.MATERIAL_ID
  5.             , O.SALES_ORDER_ITEM_ID
  6.             , sum(o.gsar) AS keyed_sales
  7.             , sum(o.sales_order_item_quantity) AS SALES_ORDER_ITEM_QTY
  8.         FROM EDP.RPT_SALES.SALES_ORDERS O
  9.         WHERE O.FISCAL_YR_ID IN (2022, 2023) AND O.WW_CHANNEL IN ('RENOVATION', 'INSTALLATION') AND o.sales_order_id = '0306231843'
  10.         GROUP BY O.SALES_ORDER_ITEM_CREATE_DATE
  11.             , O.SALES_ORDER_ID
  12.             , O.MATERIAL_ID
  13.             , O.SALES_ORDER_ITEM_ID
  14.         )
  15.     , billing AS (
  16.         SELECT B.BILLING_DOC_DATE
  17.             , B.SALES_DOC_ID
  18.             , B.MATERIAL_ID
  19.             , B.SALES_DOC_ITEM_ID
  20.             , sum(B.BILL_QTY) AS BILL_QTY
  21.             , sum(B.GSAR) AS GSAR
  22.             , row_number() OVER (
  23.                 PARTITION BY B.SALES_DOC_ID
  24.                 , B.MATERIAL_ID
  25.                 , B.SALES_DOC_ITEM_ID ORDER BY B.BILLING_DOC_DATE
  26.                 ) AS rn
  27.         FROM EDP.RPT_SALES.BILLING B
  28.         WHERE b.fiscal_yr_id IN (2022, 2023) AND b.WW_CHANNEL IN ('RENOVATION', 'INSTALLATION')
  29.         GROUP BY B.BILLING_DOC_DATE
  30.             , B.SALES_DOC_ID
  31.             , B.MATERIAL_ID
  32.             , B.SALES_DOC_ITEM_ID
  33.         )
  35. SELECT O.SALES_ORDER_ITEM_CREATE_DATE
  36.     , LTRIM(O.SALES_ORDER_ID, '0') SALES_ORDER_ID
  37.     , LTRIM(O.MATERIAL_ID, '0') MATERIAL_ID
  38.     , O.SALES_ORDER_ITEM_ID
  39.     , CASE WHEN b.rn = 1 THEN O.SALES_ORDER_ITEM_QTY ELSE 0 END AS SALES_ORDER_ITEM_QTY
  40.     , CASE WHEN b.rn = 1 THEN O.KEYED_SALES ELSE 0 END AS KEYED_SALES
  41.     , B.BILLING_DOC_DATE
  42.     , B.BILL_QTY
  43.     , B.GSAR BILLED_SALES
  44. FROM sales_orders O
  45. LEFT JOIN billing B ON O.SALES_ORDER_ID = B.SALES_DOC_ID AND O.MATERIAL_ID = B.MATERIAL_ID AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID

huangapple
  • 本文由 发表于 2023年7月11日 04:27:02
  • 转载请务必保留本文链接：https://go.coder-hub.com/76657120.html
  • left-join
  • one-to-many
  • snowflake-cloud-data-platform
  • sql
匿名

发表评论

匿名网友

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

确定