如何用SQL按月份累计统计截至对应月份的商户交易总数？

Hey there! Let's work through your problem of getting cumulative transaction counts up to each month (July 2019, August 2019, etc.) when you can only query individual month data (or have limited query capabilities). Here are a couple of solid approaches based on what you can do:

1. If you can run date-range queries (like your example) #

Your sample query already gets you the cumulative count up to June 2019 (since it's all transactions before July 1st). To expand this for each target month, just adjust the date to the first day of the next month—this gives you all transactions up to the end of the month you care about.

• For cumulative count up to July 2019:

  SELECT count(transaction_id) FROM merchants WHERE Created_at < '2019-08-01';
  

• For cumulative count up to August 2019:

  SELECT count(transaction_id) FROM merchants WHERE Created_at < '2019-09-01';
  

• Repeat this pattern: for any month YYYY-MM, use < 'YYYY-(MM+1)-01' (adjusting the month number appropriately, e.g., January becomes February, December becomes next year's January).

Bonus: Get all cumulative totals in one query (if your DB supports window functions) #

If your database (like PostgreSQL, MySQL 8+, SQL Server) supports window functions, you can generate all monthly cumulative counts in a single query—no manual running of multiple queries needed:

WITH monthly_transactions AS (
    -- First, calculate transactions per month
    SELECT
        DATE_TRUNC('month', Created_at) AS month_start,
        COUNT(transaction_id) AS monthly_count
    FROM merchants
    WHERE Created_at <= '2019-12-31' -- Adjust to your last target month
    GROUP BY month_start
)
-- Now compute cumulative totals
SELECT
    TO_CHAR(month_start, 'YYYY-MM') AS month_end,
    SUM(monthly_count) OVER (ORDER BY month_start) AS cumulative_total
FROM monthly_transactions
ORDER BY month_start;

This query first breaks down transactions by month, then uses SUM() OVER (ORDER BY ...) to roll up the counts into a running total for each month.

2. If you can only query individual month's transactions #

If your system restricts you to only fetching transactions for a single month at a time, you'll need to:

1. Fetch each month's transaction count individually
2. Manually (or via script) accumulate the totals

Step 1: Query each month's data #

• Cumulative count up to June 2019 (your base):

  SELECT count(transaction_id) FROM merchants WHERE Created_at < '2019-07-01';
  

• July 2019 transactions only:

  SELECT count(transaction_id) FROM merchants WHERE Created_at BETWEEN '2019-07-01' AND '2019-07-31 23:59:59';
  

• August 2019 transactions only:

  SELECT count(transaction_id) FROM merchants WHERE Created_at BETWEEN '2019-08-01' AND '2019-08-31 23:59:59';
  

• Repeat for each subsequent month you need.

Step 2: Calculate cumulative totals #

Add the counts step-by-step:

• Cumulative up to July: (June and earlier total) + (July monthly count)
• Cumulative up to August: (July cumulative total) + (August monthly count)
• And so on for each month.

If you want to automate this, you can use a simple script (e.g., Python) to run the queries and accumulate the totals:

# Pseudocode example—adjust based on your database connection method
def run_sql_query(query):
    # Replace with your actual code to execute SQL and return the count
    pass

# Get base cumulative count up to June 2019
cumulative_total = run_sql_query("SELECT count(transaction_id) FROM merchants WHERE Created_at < '2019-07-01'")
print(f"Cumulative transactions up to 2019-07-31: {cumulative_total}")

# Process August
august_count = run_sql_query("SELECT count(transaction_id) FROM merchants WHERE Created_at BETWEEN '2019-08-01' AND '2019-08-31 23:59:59'")
cumulative_total += august_count
print(f"Cumulative transactions up to 2019-08-31: {cumulative_total}")

# Process September and beyond the same way

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