如何忽略分秒取年月日时，通过自连接统计同小时创建日期的TestOrderItemID数量

Solution for Your Time-Based Counting Requirements #

Let's break this down into two clear parts, covering both the general hourly record count and the self-join approach for your TestOrderItemID specific case. I’ll include examples for common databases since syntax varies across systems.

1. Count Records by Truncated Time (Year/Month/Day/Hour)

First, we need to trim your datetime field down to the hour level (ignoring minutes and seconds), then group by this truncated value to get the total number of records per hour window.

Here’s how to implement this across popular databases:

• MySQL/MariaDB:

  SELECT
      DATE_FORMAT(your_datetime_column, '%Y-%m-%d %H:00:00') AS hour_window,
      COUNT(*) AS total_records
  FROM your_table
  GROUP BY hour_window
  ORDER BY hour_window;
  

• SQL Server:

  SELECT
      DATEADD(HOUR, DATEDIFF(HOUR, 0, your_datetime_column), 0) AS hour_window,
      COUNT(*) AS total_records
  FROM your_table
  GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, your_datetime_column), 0)
  ORDER BY hour_window;
  

• PostgreSQL:

  SELECT
      DATE_TRUNC('hour', your_datetime_column) AS hour_window,
      COUNT(*) AS total_records
  FROM your_table
  GROUP BY hour_window
  ORDER BY hour_window;
  

• Oracle:

  SELECT
      TRUNC(your_datetime_column, 'HH24') AS hour_window,
      COUNT(*) AS total_records
  FROM your_table
  GROUP BY TRUNC(your_datetime_column, 'HH24')
  ORDER BY hour_window;
  

2. Self-Join to Count Matching TestOrderItemIDs by Hour

For your specific need to count how many TestOrderItemIDs share the same truncated datetime (to the hour), a self-join is a clean approach. We’ll join the table to itself using the truncated hour value as the match condition, then group by each individual TestOrderItemID to get the count of matching records.

Using MySQL as an example (swap the datetime truncation function for your database if needed):

SELECT
    t1.TestOrderItemID,
    -- Optional: Show the truncated time for clarity
    DATE_FORMAT(t1.your_datetime_column, '%Y-%m-%d %H:00:00') AS truncated_create_time,
    COUNT(t2.TestOrderItemID) AS count_Oftest_sameDate
FROM your_table t1
INNER JOIN your_table t2
    ON DATE_FORMAT(t1.your_datetime_column, '%Y-%m-%d %H:00:00') = DATE_FORMAT(t2.your_datetime_column, '%Y-%m-%d %H:00:00')
GROUP BY t1.TestOrderItemID, truncated_create_time
ORDER BY truncated_create_time, t1.TestOrderItemID;

How this works:

• Each row t1 is paired with every row t2 that falls into the same hour window.
• COUNT(t2.TestOrderItemID) returns the total number of records in that hour window, which aligns perfectly with your example:
  • For TestOrderItemID 1 and 2 (same hour), the count will be 2.
  • For IDs 16, 17, and 5 (same hour), the count will be 3.

If you wanted to exclude the record itself from the count (e.g., count only other matching IDs), add AND t1.TestOrderItemID != t2.TestOrderItemID to the ON clause. But based on your example, this adjustment isn’t necessary.

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