In ClickHouse I have table product_prices
with a history of product prices. This table includes the following attributes:
CREATE TABLE product_prices
(
ProductId UInt64,
TrackedAt Date,
Price Nullable(Decimal(6, 2))
)
ENGINE MergeTree() ORDER BY (ProductId, TrackedAt)
From a predefined set of product_ids
, I need to find those, for which the following condition is satisfied:
The latest price for this product_id is lower than the penultimate one.
Example:
| ProductId | Price | TrackedAt |
|:-----------|------------:|:------------:|
| 1 | 20 | 2019-01-16 |
| 1 | 19 | 2019-01-17 |
| 2 | 5 | 2019-01-16 |
| 2 | 7 | 2019-01-17 |
I need to get
| ProductId |
|:-----------|
| 1 |
I can find the difference only for one product:
select (argMax(Price, TrackedAt) - argMin(Price, TrackedAt)) from (select ProductId, Price, TrackedAt from product_prices where ProductId = 1000 order by TrackedAt DESC limit 2)
Do you know the way I can do this?
The basic idea is to use array to capture the local states for each product. By using a subquery that returns ascending records w.r.t
TrackedAt
, you can get ascending arrays for each product.