[MariaDB] SQL WHERE challange: Izpis sprememb cen, vključno z zadnjim vnosom?

Dobro jutro.

Evo nov dan, nov lep izziv za SQL poznavalce.

V eni tabeli imam produkte, v drugi pa cene teh produktov skozi čas (one-to-many).

Takole so shranjene cene za enega od produktov:

datetime_insert        |date_hour_key|date_key|product_id   |price|
-----------------------+-------------+--------+-------------+-----+
2023-02-03 04:00:00.000|   2023020304|20230203|000-000000168| 0.49|
2023-02-03 06:00:00.000|   2023020306|20230203|000-000000168| 1.49|
2023-02-05 18:00:00.000|   2023020518|20230205|000-000000168| 0.49|
2023-02-26 13:00:00.000|   2023022613|20230226|000-000000168| 0.49|

In sedaj bi rad izpisal:
- datum in ceno prvega vnosa
- datum in ceno vsakega vnosa, kjer je prišlo do spremembe cene
- ter datum in ceno zadnjega vnosa

Query, katerega sem do sedaj skupaj spravil ( gre za rewrite tega, kar se je dogajalo tule [MariaDB] Kako izpisat "last" in "second to last" ceno vsako v svoj stolpec? ), je takšen:

WITH p AS (
	SELECT `id`, `name`,
		`datetime_insert`, `date_hour_key`, `price`,
		`dense_rank_asc`, `dense_rank_desc`
	FROM (
		SELECT
			product.`id`,
			product.`name`,
			price.`datetime_insert`,
			price.`date_hour_key`,
			price.`price`,
			DENSE_RANK() OVER (PARTITION BY product.`id` ORDER BY price.`datetime_insert` ASC) AS 'dense_rank_asc',
			DENSE_RANK() OVER (PARTITION BY product.`id` ORDER BY price.`datetime_insert` DESC) AS 'dense_rank_desc'
		FROM `database`.`product` AS product
		INNER JOIN `database`.`price` AS price
		ON product.`id` = price.`product_id`
		WHERE product.`id` IN ('000-000000168')
	) AS x
)
SELECT p.`id`,
	p.`dense_rank_asc` AS 'p', dr2.`dense_rank_asc` AS 'dr2',
	p.`name`,
	p.`price`,
	p.`datetime_insert`,
	IF (dr2.`price` IS NULL, 'null_1', dr2.`price`) AS 'price_2',
	IF (dr2.`datetime_insert` IS NULL, 'null_2', dr2.`datetime_insert`) AS 'datetime_insert_2',
	(CASE
		WHEN p.`dense_rank_asc` = 1 AND dr2.`dense_rank_asc` = 1 THEN 'init'
		WHEN p.`price` > dr2.`price` THEN 'up'
		WHEN p.`price` < dr2.`price` THEN 'down'
		WHEN p.`price` = dr2.`price` THEN 'level'
		ELSE 'unknown'
	END) AS 'up_down',
	p.`date_hour_key`
FROM p
LEFT JOIN p AS dr2 ON dr2.`id` = p.`id`
WHERE (p.`dense_rank_asc` = 1 AND dr2.`dense_rank_asc` = 1) OR
	(p.`price` != dr2.`price` AND (dr2.`dense_rank_asc` + 1) = p.`dense_rank_asc`) OR
	(p.`dense_rank_asc` = 4 AND dr2.`dense_rank_asc` = 3)	# trda koda !!!
ORDER BY p.`datetime_insert`;

Takšen pa je izpis:

id           |p|dr2|name                         |price|datetime_insert        |price_2|datetime_insert_2  |up_down|date_hour_key|
-------------+-+---+-----------------------------+-----+-----------------------+-------+-------------------+-------+-------------+
000-000000168|1|  1|LEDENI ČAJ PITAJA, SPAR, 0,5L| 0.49|2023-02-03 04:00:00.000|0.49   |2023-02-03 04:00:00|init   |   2023020304|
000-000000168|2|  1|LEDENI ČAJ PITAJA, SPAR, 0,5L| 1.49|2023-02-03 06:00:00.000|0.49   |2023-02-03 04:00:00|up     |   2023020306|
000-000000168|3|  2|LEDENI ČAJ PITAJA, SPAR, 0,5L| 0.49|2023-02-05 18:00:00.000|1.49   |2023-02-03 06:00:00|down   |   2023020518|
000-000000168|4|  3|LEDENI ČAJ PITAJA, SPAR, 0,5L| 0.49|2023-02-26 13:00:00.000|0.49   |2023-02-05 18:00:00|level  |   2023022613|

Tisto, kar me matra, je čisto na koncu spodaj, kjer je WHERE pogoj.

Z p.`dense_rank_asc` = 1 AND dr2.`dense_rank_asc` = 1 izpišem prvi vnost, kar je gut.

Z p.`price` != dr2.`price` AND (dr2.`dense_rank_asc` + 1) = p.`dense_rank_asc` izpišem vsako spremembo, kar je tudi gut.

S temle p.`dense_rank_asc` = 4 AND dr2.`dense_rank_asc` = 3 pa izpišem zadnjo vrstico, a to je trda koda... Se pravi, da bo delalo samo za tiste izpise, kjer je po 4 vnose... To sem poizkusil z MAX(p.`dense_rank_asc`) = MAX(dr2.`dense_rank_asc`) v WHERE pogoju, pa ni šlo (Invalid use of group function).


Zanima me, kako skonstruirat WHERE pogoj, da bo vedno izpisalo še zadnjo vrstico? Ali pa mogoče popravit LEFT JOIN ...


UPDATE

Ena opcija, ki sem jo sedajle naštudiral, je z uporabo GROUP BY:

INNER JOIN p AS dr2 ON dr2.`id` = p.`id`
WHERE (dr2.`dense_rank_asc` + 1) = p.`dense_rank_asc` OR dr2.`dense_rank_asc` = p.`dense_rank_asc`
GROUP BY p.`date_hour_key`
ORDER BY p.`datetime_insert`;

Ampak nisem siguren, lahko da je samo zegn, in da tako ni prav.

Ja, točno to je bila rešitev.

Zanimivo, da sem prvo to (izračun dense_rank_desc) dodal ravno s tem namenom, potem ko sem pisal pa WHERE pogoj(e), sem pa nekak zaštrikal in pozabil.

To je to. Hvala za pomoč.


Kar se tiče ta drugega, pa ugibam, da se nanaša na tole:

DENSE_RANK() OVER (PARTITION BY product.`id` ORDER BY price.`date_hour_key` DESC) AS 'dense_rank'

Na tabeli price imam takole definiran primary key:

PRIMARY KEY (`date_hour_key`,`product_id`)

Ter, product_id v price tabeli je vezan na id v product tabeli.

Ugibam, da se v takem setup-u podvajanje (1,2,2,3) za DENSE_RANK ne bi smelo zgodit.

Je pa res, da sem na opcijo ROW_NUMBER čisto pozabil.

Zaenkrat bom pustil kot je.

S temle Ćatgtp že tolko najedate, da sumim, da gre za bot-a, ki to/se promovira.

Enivej.

Kot pravi moški sem se odločil, da vseeno popravim/izboljšam query, tako da sem spremanil DENSE_RANK v ROW_NUMBER.

Sem pa našel en bug.

Npr. če se cena spremeni v zadnjem vnosu:

datetime_insert        |date_hour_key|date_key|product_id   |price|
-----------------------+-------------+--------+-------------+-----+
2023-02-03 08:00:00.000|   2023020308|20230203|000-000002813| 3.58|
2023-02-05 18:00:00.000|   2023020518|20230205|000-000002813| 3.58|
2023-03-20 12:00:00.000|   2023032012|20230320|000-000002813| 2.19|

Se na izpisu podvoji zadnji vnos:

id           |p|dr2|p_d|dr2_d|price|datetime_insert        |price_2|datetime_insert_2  |name                                       |up_down|date_hour_key|
-------------+-+---+---+-----+-----+-----------------------+-------+-------------------+-------------------------------------------+-------+-------------+
000-000002813|1|  1|  3|    3| 3.58|2023-02-03 08:00:00.000|3.58   |2023-02-03 08:00:00|INDIJSKI TRPOTEC, LUŠČINE, NATURGANIK, 100G|init   |   2023020308|
000-000002813|3|  2|  1|    2| 2.19|2023-03-20 12:00:00.000|3.58   |2023-02-05 18:00:00|INDIJSKI TRPOTEC, LUŠČINE, NATURGANIK, 100G|down   |   2023032012|
000-000002813|3|  3|  1|    1| 2.19|2023-03-20 12:00:00.000|2.19   |2023-03-20 12:00:00|INDIJSKI TRPOTEC, LUŠČINE, NATURGANIK, 100G|level  |   2023032012|

To še moram popravit, da če je pri zadnjem vnosu prišlo do spremembe, se pogoj p.`row_number_desc` = 1 AND dr2.`row_number_desc` = 1 ne sme upoštevati (vrstica "level" se ne izpiše).

V SELECT sem dodal p.`row_number_desc` AS 'p_d', dr2.`row_number_desc` AS 'dr2_d' in mislim, da bo treba tole nekako vključit v WHERE pogoj.

Trenutni WHERE pogoj:

WHERE (p.`row_number_asc` = 1 AND dr2.`row_number_asc` = 1) OR
	(p.`price` != dr2.`price` AND (dr2.`row_number_asc` + 1) = p.`row_number_asc`) OR
	(p.`row_number_desc` = 1 AND dr2.`row_number_desc` = 1)

Dobro jutro.

Ja, sem že zgoraj napisal, da sem se kasneje premislil in popravil.

Evo, tole je pa še fix z dodelanim WHERE pogojem, da, v kolikor je prišlo do spremembe cene v zadnjem vnosu, na koncu ne izpiše "level" vrstice.

WHERE (p.`row_number_asc` = 1 AND dr2.`row_number_asc` = 1) OR
	(p.`price` != dr2.`price` AND (dr2.`row_number_asc` + 1) = p.`row_number_asc`) OR
	(
		(p.`row_number_desc` = 1 AND dr2.`row_number_desc` = 1)
		AND NOT EXISTS
		(
			SELECT 1 FROM p LEFT JOIN p AS dr2 ON dr2.`id` = p.`id`
			WHERE p.`row_number_desc` = 1 AND dr2.`row_number_desc` = 2
				AND p.`price` != dr2.`price`
		)
	)