按累加列的最大值分割为行。

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

split into rows by maximum value of a accumulated column

问题

我找不到如何从累积总和中分割行的方法。我有以下结果：

  1. +---------+-----------------+---------+---------------+
  2. |ORDER_ID | DENOM_ID | DENOM_VAL | AMOUNT | MAX_PAGE_AMOUNT |
  3. +--------+--------+---------+---------+---------------+
  4. | AAAAAAAA | EUR100 | 100 | 2,800,000 | 1,000,000 |
  5. | AAAAAAAA | EUR050 | 50 | 700,000 | 1,000,000 |
  6. | AAAAAAAA | EUR010 | 10 | 150,000 | 1,000,000 |
  7. +--------+--------+---------+---------+---------------+
  • ORDER_ID：订单号
  • DENOM_ID：货币面额ID
  • DENOM_VAL：真实货币价值
  • AMOUNT：货币面额的总金额
  • MAX_PAGE_AMOUNT：每个报告页面的最大货币金额

SQL查询应生成以下行：

  1. +-------+--------+--------+---------+---------+----------+---------------+
  2. | PAG_NUM | ORDER_ID | DENOM_ID | DENOM_VAL | AMOUNT | NEW_AMOUNT | MAX_PAGE_AMOUNT |
  3. +-------+--------+--------+---------+---------+----------+---------------+
  4. | 1 | AAAAAAAA | EUR100 | 100 | 2,800,000 | 1,000,000 | 1,000,000 |
  5. | 2 | AAAAAAAA | EUR100 | 100 | 2,800,000 | 1,000,000 | 1,000,000 |
  6. | 3 | AAAAAAAA | EUR100 | 100 | 2,800,000 | 800,000 | 1,000,000 |
  7. | 3 | AAAAAAAA | EUR050 | 50 | 700,000 | 200,000 | 1,000,000 |
  8. | 4 | AAAAAAAA | EUR050 | 50 | 700,000 | 300,000 | 1,000,000 |
  9. | 4 | AAAAAAAA | EUR010 | 10 | 150,000 | 150,000 | 1,000,000 |
  10. +-------+--------+--------+---------+---------+----------+---------------+
  • PAG_NUM：页码
  • ORDER_ID：订单号
  • DENOM_ID：货币面额ID
  • DENOM_VAL：真实货币价值
  • AMOUNT：货币面额的总金额
  • NEW_AMOUNT：不能超过每页的MAX_PAGE_AMOUNT的新总金额
  • MAX_PAGE_AMOUNT：每个报告页面的最大金额

摘要：
每页的总金额不能超过MAX_PAGE_AMOUNT，当发生这种情况时，必须将AMOUNT分割成新的页面，直到不超过MAX_PAGE_AMOUNT的值。

我的当前SQL查询如下：

  1. WITH cte_dat AS (
  2.  SELECT order_id, denom_id, denom_val, amount, max_page_amount
  3.  FROM my_table
  4. ),
  5. cte_rec (order_id, denom_id, denom_val, amount, max_page_amount) AS (
  6.  SELECT order_id, denom_id, denom_val, amount, max_page_amount
  7.  FROM cte_dat
  8.  UNION ALL
  9.  SELECT order_id, denom_id, denom_val, amount, amount - max_page_amount AS amount
  10.  FROM cte_rec
  11.  WHERE (amount - max_page_amount) > 0
  12. )
  13. SELECT order_id, denom_id, denom_val, LEAST(amount, max_page_amount) AS amount, max_page_amount
  14. FROM cte_rec
  15. ORDER BY order_id, denom_val DESC, amount DESC

结果如下：

  1. +--------+--------+---------+---------+----------+---------------+
  2. | ORDER_ID | DENOM_ID | DENOM_VAL | AMOUNT | MAX_AMOUNT | MAX_PAGE_AMOUNT |
  3. +--------+--------+---------+---------+----------+---------------+
  4. | AAAAAAAA | EUR100 | 100 | 2,800,000 | 1,000,000 | 1,000,000 |
  5. | AAAAAAAA | EUR100 | 100 | 2,800,000 | 1,000,000 | 1,000,000 |
  6. | AAAAAAAA | EUR100 | 100 | 2,800,000 | 800,000 | 1,000,000 |
  7. | AAAAAAAA | EUR050 | 50 | 700,000 | 700,000 | 1,000,000 |
  8. | AAAAAAAA | EUR010 | 10 | 150,000 | 150,000 | 1,000,000 |
  9. +--------+--------+---------+---------+----------+---------------+
英文:

I can't find how to split in the rows from an accumulated sum. I have the following results:

  1. +---------+-----------------+---------+---------------+
  2. |ORDER_ID|DENOM_ID|DENOM_VAL|   AMOUNT|MAX_PAGE_AMOUNT|
  3. +--------+--------+---------+---------+---------------+
  4. |AAAAAAAA|EUR100  |      100|2,800,000|     1,000,000 |
  5. |AAAAAAAA|EUR050  |       50|  700,000|     1,000,000 |
  6. |AAAAAAAA|EUR010  |       10|  150,000|     1,000,000 |
  7. +--------+--------+---------+---------+---------------+
  • ORDER_ID: Order number
  • DENOM_ID: Currency denomination ID
  • DENOM_VAL: Real currency value
  • AMOUNT: Total amount of currency denomination
  • MAX_PAGE_AMOUNT: Max. currency per report page

The SQL query should generate the following rows:

  1. +-------+--------+--------+---------+---------+----------+---------------+
  2. |PAG_NUM|ORDER_ID|DENOM_ID|DENOM_VAL|   AMOUNT|NEW_AMOUNT|MAX_PAGE_AMOUNT|
  3. +-------+--------+--------+---------+---------+----------+---------------+
  4. |      1|AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
  5. |      2|AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
  6. |      3|AAAAAAAA|EUR100  |      100|2,800,000|   800,000|      1,000,000|
  7. |      3|AAAAAAAA|EUR050  |       50|  700,000|   200,000|      1,000,000|
  8. |      4|AAAAAAAA|EUR050  |       50|  700,000|   300,000|      1,000,000|
  9. |      4|AAAAAAAA|EUR010  |       10|  150,000|   150,000|      1,000,000|
  10. +-------+--------+--------+---------+---------+----------+---------------+
  • PAG_NUM: Page number
  • ORDER_ID: Order number
  • DENOM_ID: Currency denomination ID
  • DENOM_VAL: Real currency value
  • AMOUNT: Total amount of currency denomination
  • NEW_AMOUNT: New total amount that cannot exceed MAX_PAGE_AMOUNT per page
  • MAX_PAGE_AMOUNT: Max. amount by report page

Summary:
The summed amounts per page cannot exceed MAX_PAGE_AMOUNT, when that occurs AMOUNT must be divided/split in a new page until it does not exceed the value of MAX_PAGE_AMOUNT.

My current SQL is:

  1. WITH cte_dat AS (
  2.  SELECT order_id, denom_id, denom_val, amount, max_page_amount
  3.  FROM my_table
  4. ),
  5. cte_rec (order_id, denom_id, denom_val, amount, max_page_amount) AS (
  6.  SELECT order_id, denom_id, denom_val, amount, max_page_amount
  7.  FROM cte_dat
  8.  UNION ALL
  9.  SELECT order_id, denom_id, denom_val, amount, amount - max_page_amount AS amount
  10.  FROM cte_rec
  11.  WHERE (amount - max_page_amount) > 0
  12. )
  13. SELECT order_id, denom_id, denom_val, LEAST(amount, max_page_amount) AS amount, max_page_amount
  14. FROM cte_rec
  15. ORDER BY order_id, denom_val DESC, amount DESC

Results:

  1. +--------+--------+---------+---------+----------+---------------+
  2. |ORDER_ID|DENOM_ID|DENOM_VAL|   AMOUNT|MAX_AMOUNT|MAX_PAGE_AMOUNT|
  3. +--------+--------+---------+---------+----------+---------------+
  4. |AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
  5. |AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
  6. |AAAAAAAA|EUR100  |      100|2,800,000|   800,000|      1,000,000|
  7. |AAAAAAAA|EUR050  |       50|  700,000|   700,000|      1,000,000|
  8. |AAAAAAAA|EUR010  |       10|  150,000|   150,000|      1,000,000|
  9. +--------+--------+---------+---------+----------+---------------+

答案1

得分: 3

您可以使用分析函数生成金额的累积总数，并使用递归查询将其分割成页面：

  1. WITH cte_rec (
  2.   order_id,
  3.   denom_id,
  4.   denom_val,
  5.   amount,
  6.   max_page_amount,
  7.   prev_total,
  8.   total,
  9.   start_page,
  10.   end_page
  11. ) AS (
  12.   SELECT order_id,
  13.          denom_id,
  14.          denom_val,
  15.          amount,
  16.          max_page_amount,
  17.          SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM) - amount,
  18.          SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM),
  19.          GREATEST(
  20.            CEIL(
  21.              (SUM(amount)
  22.                 OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM) - amount)
  23.              / max_page_amount
  24.            ),
  25.            1
  26.          ),
  27.          CEIL(
  28.            SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount, ROWNUM)
  29.            / max_page_amount
  30.          )
  31.   FROM   my_table
  32. UNION ALL
  33.   SELECT order_id,
  34.          denom_id,
  35.          denom_val,
  36.          amount,
  37.          max_page_amount,
  38.          prev_total,
  39.          total,
  40.          start_page + 1,
  41.          end_page
  42.   FROM   cte_rec
  43.   WHERE  start_page < end_page
  44. )
  45. SEARCH DEPTH FIRST BY order_id, denom_val DESC, amount DESC SET o_id
  46. SELECT start_page AS page_num,
  47.        order_id,
  48.        denom_id,
  49.        denom_val,
  50.        amount,
  51.        LEAST(start_page * max_page_amount, total)
  52.          - GREATEST((start_page - 1) * max_page_amount, prev_total) AS new_amount,
  53.        max_page_amount
  54. FROM   cte_rec;

对于示例数据：

  1. CREATE TABLE my_table (ORDER_ID, DENOM_ID, DENOM_VAL, AMOUNT, MAX_PAGE_AMOUNT) AS
  2. SELECT 'AAAAAAAA', 'EUR100', 100, 2800000, 1000000 FROM DUAL UNION ALL
  3. SELECT 'AAAAAAAA', 'EUR050',  50,  700000, 1000000 FROM DUAL UNION ALL
  4. SELECT 'AAAAAAAA', 'EUR010',  10,  150000, 1000000 FROM DUAL;

输出如下：

PAGE_NUM ORDER_ID DENOM_ID DENOM_VAL AMOUNT NEW_AMOUNT MAX_PAGE_AMOUNT
1 AAAAAAAA EUR100 100 2800000 1000000 1000000
2 AAAAAAAA EUR100 100 2800000 1000000 1000000
3 AAAAAAAA EUR100 100 2800000 800000 1000000
3 AAAAAAAA EUR050 50 700000 200000 1000000
4 AAAAAAAA EUR050 50 700000 500000 1000000
4 AAAAAAAA EUR010 10 150000 150000 1000000

fiddle

英文:

You can use an analytic function to generate the running totals for the amounts and use a recursive query to split that into pages:

  1. WITH cte_rec (
  2.   order_id,
  3.   denom_id,
  4.   denom_val,
  5.   amount,
  6.   max_page_amount,
  7.   prev_total,
  8.   total,
  9.   start_page,
  10.   end_page
  11. ) AS (
  12.   SELECT order_id,
  13.          denom_id,
  14.          denom_val,
  15.          amount,
  16.          max_page_amount,
  17.          SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM) - amount,
  18.          SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM),
  19.          GREATEST(
  20.            CEIL(
  21.              (SUM(amount)
  22.                 OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM) - amount)
  23.              / max_page_amount
  24.            ),
  25.            1
  26.          ),
  27.          CEIL(
  28.            SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount, ROWNUM)
  29.            / max_page_amount
  30.          )
  31.   FROM   my_table
  32. UNION ALL
  33.   SELECT order_id,
  34.          denom_id,
  35.          denom_val,
  36.          amount,
  37.          max_page_amount,
  38.          prev_total,
  39.          total,
  40.          start_page + 1,
  41.          end_page
  42.   FROM   cte_rec
  43.   WHERE  start_page < end_page
  44. )
  45. SEARCH DEPTH FIRST BY order_id, denom_val DESC, amount DESC SET o_id
  46. SELECT start_page AS page_num,
  47.        order_id,
  48.        denom_id,
  49.        denom_val,
  50.        amount,
  51.        LEAST(start_page * max_page_amount, total)
  52.          - GREATEST((start_page - 1) * max_page_amount, prev_total) AS new_amount,
  53.        max_page_amount
  54. FROM   cte_rec;

Which, for the sample data:

  1. CREATE TABLE my_table (ORDER_ID, DENOM_ID, DENOM_VAL, AMOUNT, MAX_PAGE_AMOUNT) AS
  2. SELECT 'AAAAAAAA', 'EUR100', 100, 2800000, 1000000 FROM DUAL UNION ALL
  3. SELECT 'AAAAAAAA', 'EUR050',  50,  700000, 1000000 FROM DUAL UNION ALL
  4. SELECT 'AAAAAAAA', 'EUR010',  10,  150000, 1000000 FROM DUAL;

Outputs:

PAGE_NUM ORDER_ID DENOM_ID DENOM_VAL AMOUNT NEW_AMOUNT MAX_PAGE_AMOUNT
1 AAAAAAAA EUR100 100 2800000 1000000 1000000
2 AAAAAAAA EUR100 100 2800000 1000000 1000000
3 AAAAAAAA EUR100 100 2800000 800000 1000000
3 AAAAAAAA EUR050 50 700000 200000 1000000
4 AAAAAAAA EUR050 50 700000 500000 1000000
4 AAAAAAAA EUR010 10 150000 150000 1000000

fiddle

答案2

得分: 0

以下是已翻译好的内容：

使用 CONNECT BY LEVEL 根据金额除以 max_page_amount 生成所需的行数。

  1. SELECT
  2.       order_id
  3.     , denom_id
  4.     , denom_val
  5.     , amount
  6.     , max_page_amount
  7. FROM my_table CONNECT BY LEVEL <= CEIL(amount / max_page_amount)
  8. WHERE PRIOR order_id = order_id
  9.     AND PRIOR denom_id = denom_id
  10.     AND PRIOR denom_val = denom_val
  11.     AND PRIOR amount = amount
  12.     AND PRIOR max_page_amount = max_page_amount

CEIL 用于确保即使除法不精确，所有行也被包括在内，而 PRIOR 关键字用于确保重复的行具有与原始行相同的值。

如果您不喜欢使用 Oracle 特定的函数，那么使用 "递归 CTE" 也可以工作：

  1. WITH CTE
  2. AS (
  3.     SELECT order_id, denom_id, denom_val, amount, max_page_amount, 1 AS lvl
  4.     FROM my_table
  5.     
  6.     UNION ALL
  7.     
  8.     SELECT order_id, denom_id, denom_val, amount, max_page_amount, lvl + 1
  9.     FROM CTE
  10.     WHERE lvl < CEIL(amount / max_page_amount)
  11.     )
  12. SELECT
  13.       order_id
  14.     , denom_id
  15.     , denom_val
  16.     , amount
  17.     , max_page_amount
  18. FROM CTE;
英文:

Use CONNECT BY LEVEL to generate the necessary number of rows based on the division of amount by max_page_amount.

  1. SELECT
  2.       order_id
  3.     , denom_id
  4.     , denom_val
  5.     , amount
  6.     , max_page_amount
  7. FROM my_table CONNECT BY LEVEL <= CEIL(amount / max_page_amount)
  8. WHERE PRIOR order_id = order_id
  9.     AND PRIOR denom_id = denom_id
  10.     AND PRIOR denom_val = denom_val
  11.     AND PRIOR amount = amount
  12.     AND PRIOR max_page_amount = max_page_amount

CEIL is used to ensure that all the rows are included even if the division is not exact, and the PRIOR keyword is used to ensure that the repeated rows have the same values as the original row.

If you prefer not to use Oracle specific functions, then using a "recursive CTE" should also work:

  1. WITH CTE
  2. AS (
  3.     SELECT order_id, denom_id, denom_val, amount, max_page_amount, 1 AS lvl
  4.     FROM my_table
  5.     
  6.     UNION ALL
  7.     
  8.     SELECT order_id, denom_id, denom_val, amount, max_page_amount, lvl + 1
  9.     FROM CTE
  10.     WHERE lvl < CEIL(amount / max_page_amount)
  11.     )
  12. SELECT
  13.       order_id
  14.     , denom_id
  15.     , denom_val
  16.     , amount
  17.     , max_page_amount
  18. FROM CTE;

答案3

得分: 0

这是您提供的代码段的翻译：

基于数据区间和最大页数 max_page_amount 的计算，还有另一种解决方案，可以比较在您的数据集上的性能。在这里，递归仅用于生成桶：

  1. 在区间中（intervals）作为 (
  2.     选择 d.*, lag(sum_amount,1,0) over(order by rn) 作为 start_interval, sum_amount as end_interval
  3.     从 (
  4.         选择 rownum as rn,  order_id, denom_id, denom_val, amount, max_page_amount,
  5.         sum(amount) over(order by rownum) as sum_amount
  6.         从 my_table 
  7.     ) d
  8. )
  9. , maxdata 作为 (
  10.     选择 max_page_amount, sum(amount) as max_amount 从 my_table1
  11.     按 max_page_amount 分组
  12. )
  13. , buckets 作为 (
  14.     选择 level as bucket_id, (level-1) * max_page_amount as start_bucket, level * max_page_amount as end_bucket 
  15.     从 maxdata
  16.     连接通过 (level-1) * max_page_amount < max_amount 
  17. )
  18. 选择 bucket_id as page_num, order_id, denom_id, denom_val, amount, max_page_amount,
  19.     案例当 end_interval > end_bucket 
  20.         然后 
  21.             最小值(max_page_amount, end_bucket - start_interval)
  22.         否则
  23.             最小值(amount, end_interval - start_bucket)
  24.     结束作为 new_page_amount
  25. 从 intervals
  26. 加入 buckets 在 start_bucket <= end_interval 和 end_bucket >= start_interval
  27. ;

请注意，我只提供了代码的翻译部分，没有回答其他问题。如果您需要进一步的翻译或解释，请告诉我。

英文:

There is another solution based on calculation of intervals on the data and buckets of max_page_amount, it could be interesting to compare the performances on your data set, here recursion is used only to generate the buckets:

  1. with intervals as (
  2. 	select d.*, lag(sum_amount,1,0) over(order by rn) as start_interval, sum_amount as end_interval
  3. 	from (
  4. 		select rownum as rn,  order_id, denom_id, denom_val, amount, max_page_amount,
  5. 		sum(amount) over(order by rownum) as sum_amount
  6. 		from my_table 
  7. 	) d
  8. )
  9. , maxdata as (
  10. 	select max_page_amount, sum(amount) as max_amount from my_table1
  11. 	group by max_page_amount
  12. )
  13. , buckets as (
  14. 	select level as bucket_id, (level-1) * max_page_amount as start_bucket, level * max_page_amount as end_bucket 
  15. 	from maxdata
  16. 	connect by (level-1) * max_page_amount < max_amount 
  17. )
  18. select bucket_id as page_num, order_id, denom_id, denom_val, amount, max_page_amount,
  19. 	case when end_interval > end_bucket 
  20. 		then 
  21. 			least(max_page_amount, end_bucket - start_interval)
  22. 		else
  23. 			least(amount, end_interval - start_bucket)
  24. 	end as new_page_amount
  25. from intervals
  26. join buckets on start_bucket <= end_interval and end_bucket >= start_interval
  27. ;

huangapple
  • 本文由 发表于 2023年5月26日 11:52:09
  • 转载请务必保留本文链接：https://go.coder-hub.com/76337552.html
  • common-table-expression
  • oracle
  • recursive-query
  • sql
  • window-functions
匿名

发表评论

匿名网友

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

确定