Amazon Athena SQL查询：按日期范围统计指定表行数

Got it, let's work through this Amazon Athena SQL task together. Below are two targeted query solutions to count rows in your T2, T3, T4, T5, and T7 tables—one for the most recent loaded records, and another for a specific date range.

Assumption #

First, I'm assuming each of these tables has a timestamp/date field that tracks when records were loaded (e.g., load_timestamp, insert_date, or a partition column like dt). If your tables use a different identifier for load batches (like batch_id), you can adjust the queries accordingly.

1. Count Rows from the Most Recent Load #

This query first identifies the latest load time for each table, then counts only the records loaded at that time:

WITH latest_load_times AS (
    SELECT 'T2' AS table_name, MAX(load_timestamp) AS latest_load_time FROM T2
    UNION ALL
    SELECT 'T3' AS table_name, MAX(load_timestamp) AS latest_load_time FROM T3
    UNION ALL
    SELECT 'T4' AS table_name, MAX(load_timestamp) AS latest_load_time FROM T4
    UNION ALL
    SELECT 'T5' AS table_name, MAX(load_timestamp) AS latest_load_time FROM T5
    UNION ALL
    SELECT 'T7' AS table_name, MAX(load_timestamp) AS latest_load_time FROM T7
)
SELECT 
    l.table_name,
    COUNT(*) AS row_count
FROM (
    SELECT 'T2' AS table_name, load_timestamp FROM T2
    UNION ALL
    SELECT 'T3' AS table_name, load_timestamp FROM T3
    UNION ALL
    SELECT 'T4' AS table_name, load_timestamp FROM T4
    UNION ALL
    SELECT 'T5' AS table_name, load_timestamp FROM T5
    UNION ALL
    SELECT 'T7' AS table_name, load_timestamp FROM T7
) AS all_records
JOIN latest_load_times l 
    ON all_records.table_name = l.table_name 
    AND all_records.load_timestamp = l.latest_load_time
GROUP BY l.table_name
ORDER BY l.table_name;

• Adjustment Tip: If your tables use a batch ID instead of a timestamp, replace MAX(load_timestamp) with MAX(batch_id) and update the join condition to match.

2. Count Rows Within a Specified Date Range #

This query lets you define a start and end time to count records loaded in that window. We use a half-open interval (< end_date + 1 day) to avoid missing records from the final second of your target range:

SELECT 
    'T2' AS table_name,
    COUNT(*) AS row_count
FROM T2
WHERE load_timestamp >= TIMESTAMP '2024-01-01 00:00:00' 
  AND load_timestamp < TIMESTAMP '2024-02-01 00:00:00'
UNION ALL
SELECT 
    'T3' AS table_name,
    COUNT(*) AS row_count
FROM T3
WHERE load_timestamp >= TIMESTAMP '2024-01-01 00:00:00' 
  AND load_timestamp < TIMESTAMP '2024-02-01 00:00:00'
UNION ALL
SELECT 
    'T4' AS table_name,
    COUNT(*) AS row_count
FROM T4
WHERE load_timestamp >= TIMESTAMP '2024-01-01 00:00:00' 
  AND load_timestamp < TIMESTAMP '2024-02-01 00:00:00'
UNION ALL
SELECT 
    'T5' AS table_name,
    COUNT(*) AS row_count
FROM T5
WHERE load_timestamp >= TIMESTAMP '2024-01-01 00:00:00' 
  AND load_timestamp < TIMESTAMP '2024-02-01 00:00:00'
UNION ALL
SELECT 
    'T7' AS table_name,
    COUNT(*) AS row_count
FROM T7
WHERE load_timestamp >= TIMESTAMP '2024-01-01 00:00:00' 
  AND load_timestamp < TIMESTAMP '2024-02-01 00:00:00'
ORDER BY table_name;

• Adjustment Tip: If your tables use a DATE type field instead of a timestamp, swap the TIMESTAMP literals with date strings and use BETWEEN (or keep the half-open interval with load_date >= '2024-01-01' AND load_date < '2024-02-01').
• Partitioned Tables: If your tables are partitioned by date (e.g., dt column), add the partition filter to the WHERE clause (e.g., dt = '2024-01-31') to drastically speed up the query by avoiding full table scans.

Bonus: Quick Total Row Count (All Loads) #

If you ever need the total number of rows in each table regardless of load time, use this simplified query:

SELECT 'T2' AS table_name, COUNT(*) AS total_rows FROM T2
UNION ALL
SELECT 'T3' AS table_name, COUNT(*) AS total_rows FROM T3
UNION ALL
SELECT 'T4' AS table_name, COUNT(*) AS total_rows FROM T4
UNION ALL
SELECT 'T5' AS table_name, COUNT(*) AS total_rows FROM T5
UNION ALL
SELECT 'T7' AS table_name, COUNT(*) AS total_rows FROM T7
ORDER BY table_name;

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