如何在Pandas中生成多行数据？求助实现counts列倒计时的order列

Hey there! Let's figure out how to add an order column that counts down from the counts value in each row, while generating the corresponding number of rows for each original entry. Below are practical implementations for common databases:

1. For PostgreSQL (Using generate_series) #

PostgreSQL has a handy built-in function that makes this task straightforward:

SELECT
  ot.id,
  ot.name,
  ot.counts,
  gs.order
FROM original_table ot
CROSS JOIN generate_series(1, ot.counts) AS gs(order)
ORDER BY ot.id, gs.order DESC;

How this works: #

• CROSS JOIN generate_series(1, ot.counts) creates a new row for every integer from 1 to the counts value of each original row.
• Sorting by gs.order DESC flips the ascending sequence into a countdown (e.g., if counts is 3, the order values become 3, 2, 1).

2. For MySQL 8.0+ (Using Recursive CTE) #

MySQL doesn't have generate_series, but we can use a recursive Common Table Expression (CTE) to achieve the same result:

WITH RECURSIVE countdown AS (
  -- Base case: Start with the full counts value as the initial order
  SELECT
    id,
    name,
    counts,
    counts AS order
  FROM original_table
  WHERE counts > 0
  UNION ALL
  -- Recursive case: Decrement the order until we reach 1
  SELECT
    c.id,
    c.name,
    c.counts,
    c.order - 1
  FROM countdown c
  WHERE c.order > 1
)
SELECT id, name, counts, order
FROM countdown
ORDER BY id, order DESC;

How this works: #

• The base part of the CTE selects each row from your original table and sets order equal to counts.
• The recursive part takes each existing row in the CTE and creates a new row with order reduced by 1, stopping when order hits 1.
• Finally, we sort to ensure the countdown order is correct.

3. For SQL Server (Using Recursive CTE or GENERATE_SERIES 2022+) #

If you're on SQL Server 2022 or later, you can use GENERATE_SERIES similar to PostgreSQL:

SELECT
  ot.id,
  ot.name,
  ot.counts,
  gs.value AS order
FROM original_table ot
CROSS JOIN GENERATE_SERIES(1, ot.counts) gs
ORDER BY ot.id, gs.value DESC;

For older SQL Server versions, use a recursive CTE approach identical to the MySQL example above.

内容的提问来源于stack exchange，提问作者Minh Tran