Data looks like
| PONum | ReplacementDate | PriceChangeDate | PcrPreviousPriceValue | PcrPriceValue |
|---|---|---|---|---|
| 90358 | 2022-01-10T06:16:17.420000 | 2020-05-04 08:19:49.746500 | 483.93 | 483.07 |
| 90358 | 2022-01-10T06:16:17.420000 | 2020-04-19 08:25:45.122000 | 332.19 | 332.84 |
| 90358 | 2022-01-10T06:16:17.420000 | 2020-06-20 01:27:55.657500 | 434.56 | 430.18 |
| 90358 | 2022-01-10T06:16:17.420000 | 2021-01-20 16:53:31.762600 | 403.81 | 399.75 |
Expected result:
| PONum | ReplacementDate | PriceChangeDate | PcrPreviousPriceValue | PcrPriceValue |
|---|---|---|---|---|
| 90358 | 2022-01-10T06:16:17.420000 | 2021-01-20 16:53:31.762600 | 403.81 | 399.75 |
I know in SQL Server cross apply can be used to get the data, how can we do the same in GBQ?
CodePudding user response：
Assuming price change is not faster than once per day.
WITH CTE AS (
SELECT YT.*,
row_number() over (PARTITION BY PONum ORDER by DATE_DIFF(priceChangeDate, ReplacementDate, DAY) RN
FROM YourTable YT)
SELECT *
FROM CTE
WHERE RN=1
CodePudding user response：
SELECT top 1 * FROM Your_Table_Name ORDER BY DATEDIFF(SECOND, [PriceChangeDate], [ReplacementDate]) / 1000