Есть задача, на визуальных элементах отображать значения собственной цены и цены для клиента, ну другие производные показатели. Пытался решать разными способами и DAX запросами и в power query создавая кросс таблицу дат-артикулов и последующие джоины и заполнения. Хочу это дело вывести в запрос postgresql, но не получается...
Входные данные:
Таблица товаров, в моем примере просто столбец артикулов. (арт1, арт2, ...)
Вторая и третья таблицы - таблицы изменения цены со столбцами: дата, артикул, цена ((2023-01-01, арт1, 100), (2023-01-01, арт2, 105), (2024-01-01, арт1, 150),...)
Вкратце: целью запроса создать таблицу в которой на каждую дату по артикулу указана последняя измененная цена/цена клиента.
Пример моего запроса:
CREATE TABLE IF NOT EXISTS items (
article character varying PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS self_prices_history (
updated_at timestamp NOT NULL,
article character varying NOT NULL,
value numeric,
CONSTRAINT self_prices_history_pk PRIMARY KEY (updated_at, article)
);
CREATE TABLE IF NOT EXISTS client_prices_history (
updated_at timestamp NOT NULL,
article character varying NOT NULL,
value numeric,
CONSTRAINT client_prices_history_pk PRIMARY KEY (updated_at, article)
);
INSERT INTO items VALUES ('001'), ('002'), ('003') ('004');
INSERT INTO client_prices_history VALUES
('2024-07-16 09:10:19.950802', '001', 1100),
('2024-07-16 15:10:05.299459', '001', 1147),
('2024-07-17 21:10:16.538367', '001', 1142),
('2024-07-19 09:10:20.937887', '001', 1136),
('2024-07-19 15:15:38.553837', '001', 1133),
('2024-07-20 15:10:05.43201', '001', 1042),
('2024-08-25 15:10:22.056807', '002', 1181),
('2024-08-27 21:10:38.562463', '002', 1122),
('2024-08-29 15:10:28.421389', '002', 1120),
('2024-08-30 21:10:21.551706', '002', 1105),
('2024-09-02 15:10:05.561434', '002', 1107),
('2024-08-07 09:31:15.799588', '003', 6209),
('2024-08-07 15:10:05.359173', '003', 6231),
('2024-08-08 03:10:11.48272', '003', 3993),
('2024-08-10 15:10:20.400104', '003', 6268),
('2024-08-11 15:10:08.059679', '003', 6283),
('2024-08-12 15:10:04.702603', '003', 6285);
INSERT INTO self_prices_history VALUES
('2023-12-22 22:00:06.116714', '001', 1604),
('2024-07-12 21:00:16.216461', '001', 1652),
('2024-08-02 18:00:18.123571', '001', 1710),
('2024-08-10 15:30:52.816604', '001', 1736),
('2024-09-02 11:00:16.306556', '001', 1790),
('2023-07-14 09:59:32.623492', '002', 2031),
('2023-08-25 09:06:38.553189', '002', 2031),
('2023-08-27 12:22:25.965515', '002', 2031),
('2023-08-30 09:03:19.702678', '002', 2031),
('2023-08-31 09:02:24.267454', '002', 2031),
('2023-07-14 09:59:32.582678', '003', 7207),
('2023-08-18 08:56:21.850859', '003', 7568),
('2023-08-25 09:06:38.553189', '003', 7568),
('2023-08-27 12:22:25.965515', '003', 7568),
('2023-08-28 08:59:35.525273', '003', 7568);
with ds AS (
SELECT generate_series(
'2023-01-01',
'2025-12-31',
'1 day'::interval
)::date AS date
),
items AS (
SELECT article FROM items
),
all_combinations AS (
SELECT ds.date, items.article
FROM ds
CROSS JOIN items
),
client_prices AS (
SELECT
date(updated_at) as date,
article,
value as client_price
FROM client_prices_history
),
self_prices AS (
SELECT
date(updated_at) as date,
article,
value as self_price
FROM self_prices_history
),
combined_prices AS (
SELECT
all_combinations.date,
all_combinations.article,
cp.client_price,
sp.self_price,
MAX(cp.client_price) OVER (
PARTITION BY all_combinations.article ORDER BY all_combinations.date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_client_price,
MAX(sp.self_price) OVER (
PARTITION BY all_combinations.article ORDER BY all_combinations.date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_self_price,
COUNT(*) OVER (PARTITION BY all_combinations.article) as count_rows
FROM all_combinations
LEFT JOIN self_prices sp
ON all_combinations.date = sp.date
AND all_combinations.article = sp.article
LEFT JOIN client_prices cp
ON all_combinations.date = cp.date
AND all_combinations.article = cp.article
)
SELECT
date,
article,
COALESCE(filled_client_price, 0) AS client_price,
COALESCE(filled_self_price, 0) AS self_price,
count_rows
FROM combined_prices
WHERE date between '2024-07-14' and '2024-08-30'
ORDER BY date, article;
Но данные запрос не заполняет пустые значения. Подскажите что не так в запросе?
Пример, где не тот результат и чего хочу сделать. Т.е. нужно заполнить пустые значения даже для тех дат, которые были ранее самого первого изменения цены артикула.
Простой
