基于商品交易所历史交易列表确定离散时段每小时价格

Hey there! Let's walk through how to generate that hourly-dimension price list from your discrete trade records. First, let's restate the core requirement clearly: we have a list of historical trades (each with a datetime and price), and we need to output an hourly list with opening and closing prices following three specific rules. Here's a detailed breakdown of the logic and how to apply it:

First, a quick prerequisite: make sure your trade records are sorted chronologically by datetime. This is critical for accurately identifying first/last trades in a window and finding the most recent prior price when needed.

Key Scenarios & Handling Rules #

• Scenario 1: Only 1 trade in the hour window
  The trade's price becomes both the opening and closing price for that hour. Simple enough—no extra work here beyond grabbing that single price.

• Scenario 2: Multiple trades in the hour window
  Take the price of the first trade in the hour as the opening price, and the price of the last trade in the hour as the closing price. Just make sure your trades are sorted so you can reliably pick the first and last entries in the hourly group.

• Scenario 3: No trades in the hour window
  Both opening and closing prices are set to the price of the most recent trade before this hour. If there are no prior trades at all (e.g., the first hour in your dataset has no trades), you'll need to decide on a fallback (like null, or a default price—this depends on your business needs).

Example Walkthrough #

Let's use a sample dataset to make this concrete:

Trade records (sorted):

• 2024-05-20 09:12:00 | $98
• 2024-05-20 09:37:00 | $101
• 2024-05-20 11:22:00 | $103

Hourly results:

• 09:00-10:00: Opening $98, Closing $101 (Scenario 2: multiple trades)
• 10:00-11:00: Opening $101, Closing $101 (Scenario 3: no trades, use last prior price)
• 11:00-12:00: Opening $103, Closing $103 (Scenario 1: single trade)

Quick Implementation Tips #

If you're coding this out, here's a rough workflow to follow:

1. Generate your hourly time range: Define all the hourly buckets you need to cover (e.g., from the hour of the first trade to the hour of the last trade, or a fixed date range).
2. Group trades by hour: Use datetime functions to bucket each trade into its corresponding hourly window.
3. Process each hour:
   • For each hour, check if there are trades in the bucket.
   • Apply the matching scenario rule to set open/close prices.
   • For Scenario 3, keep track of the "last known price" as you iterate through hours, updating it only when a trade occurs.

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