请求协助：获取MSFT每日首笔与末笔交易价格的SQL实现

Hey there, let's work through this problem together—first fixing your SQL query, then looking at potential ER model issues to get you the daily open (first trade) and close (last trade) prices for MSFT.

First: Fix Your SQL Query #

Your core problem is not grouping by the trading day to fetch the earliest/latest trades per day, plus it looks like your Trans.date field is actually the trade time (e.g., 09:00, 16:00), while the actual trading date is stored in Session.date. That's why your previous Min(date)/Max(date) calls were pulling global earliest/latest times instead of per-day values.

Recommended Query (Using Window Functions for Clarity) #

This approach uses CTEs and window functions to rank trades by time per day, then pulls the first and last prices:

WITH daily_trades AS (
    SELECT 
        s.date AS trade_date,
        t.PriceofShare,
        t.date AS trade_time,
        -- Rank trades from earliest to latest per day
        ROW_NUMBER() OVER (PARTITION BY s.date ORDER BY t.date ASC) AS open_rank,
        -- Rank trades from latest to earliest per day
        ROW_NUMBER() OVER (PARTITION BY s.date ORDER BY t.date DESC) AS close_rank
    FROM Trans t
    JOIN Session s ON t.Sdate = s.date
    JOIN Orders o ON t.Sdate = o.Sdate AND o.SID = 'MSFT'
)
SELECT 
    trade_date,
    -- Grab the first trade price of the day
    MAX(CASE WHEN open_rank = 1 THEN PriceofShare END) AS opening_price,
    -- Grab the last trade price of the day
    MAX(CASE WHEN close_rank = 1 THEN PriceofShare END) AS closing_price
FROM daily_trades
GROUP BY trade_date;

Why Your Previous Queries Failed #

• Your first query returned all MSFT trades without filtering for the first/last of the day, so you couldn't distinguish open/close prices.
• Your second query used SELECT Min(date) FROM Trans which gets the global earliest trade time across all days, not the earliest time per trading day. Additionally, UNION merges rows instead of putting open/close prices on the same line for each day.

ER Model Checks & Reference #

If the query above still doesn't work, your table relationships might be misaligned. Here's a standard ER model for stock trading systems to compare against your setup:

Core Entities & Relationships #

• Session (Trading Day)
  • Fields: date (primary key, e.g., '2024-05-20'), start_time, end_time
  • Represents a single trading day
• Orders (Stock Orders)
  • Fields: order_id (primary key), SID (stock ticker, e.g., 'MSFT'), Sdate (foreign key to Session.date), order_time, quantity
  • Links to the trading day and stock being traded
• Trans (Trade Executions)
  • Fields: trans_id (primary key), order_id (foreign key to Orders.order_id), Sdate (foreign key to Session.date), date (trade time, e.g., '09:00:00'), PriceofShare (execution price)
  • Represents a completed trade for an order

Key Relationship Rules to Verify #

• Each Session can have multiple Orders (all orders placed that day)
• Each Order can have multiple Trans (partial fills or multiple executions)
• You must filter Orders.SID = 'MSFT' first, then link to corresponding Trans and Session records

Common ER Model Issues to Check #

1. Missing Foreign Keys: If Trans doesn't link to Orders (via order_id), you might be pulling trades unrelated to MSFT orders.
2. Date/Time Format Mismatch: If Session.date is a date-only field and Trans.Sdate is a datetime, your joins will fail. Ensure they use the same date format.
3. Incorrect Field Names: If Trans.date is actually a full datetime (date + time), use DATE(t.date) to extract the trading day for grouping.

Quick Debug Queries #

To confirm your data is structured correctly:

1. Check if the 09:00 MSFT trade exists:

SELECT * FROM Trans t
JOIN Orders o ON t.Sdate = o.Sdate
WHERE o.SID = 'MSFT' AND t.date = '09:00:00.0000000';

2. Verify earliest/latest times per trading day:

SELECT s.date, MIN(t.date) AS earliest_trade, MAX(t.date) AS latest_trade
FROM Trans t
JOIN Session s ON t.Sdate = s.date
JOIN Orders o ON t.Sdate = o.Sdate AND o.SID = 'MSFT'
GROUP BY s.date;

内容的提问来源于stack exchange，提问作者Samer.M.A