[MariaDB] Kako izpisat "last" in "second to last" ceno vsako v svoj stolpec?

Dobro jutro.


Evo, po nekaj dneh premora nov dan, nov SQL izziv.

Situacija je taka. V MariaDB bazi imam dve tabeli; ena ima produkte, druga pa cene (več njih, skozi čas). Tabeli sta povezani on-to-many.


Sedaj pa bi rad izpisal zadni dve ceni (ter datum teh cen), pri čemer bi:
- zadnja cena bila izpisana v last_price stolpec
- predzanja cena pa v second_to_last_price stolpec


Trenutno imam polovični sql, s katerim lahko z uporabo funkcije DENSE_RANK izberem vrstice, ki imajo vrednost 1 (last) ali 2 (second to last).

SELECT * FROM (
	SELECT
		DENSE_RANK() OVER (PARTITION BY product.`id` ORDER BY price.`date_hour_key` DESC) AS 'dense_rank',
		product.`id`,
		product.`product_name`,
		price.`price`,
		price.`date_hour_key`
	FROM `database`.`products` AS product
	INNER JOIN `database`.`prices` AS price
	ON product.`id` = price.`product_id`
	WHERE product.`id` IN ('000-000012278', '000-000000588', '000-000001588')
) AS x
ORDER BY x.`id` ASC, x.`dense_rank` ASC;

In še trenutni izpis:

dense_rank|id           |product_name                                   |price|date_hour_key|
----------+-------------+-----------------------------------------------+-----+-------------+
         1|000-000000588|PARADIŽNIKOVA OMAKA Z OLIVAMI PESTO, SPAR, 190G|1,58 |   2023022613|
         2|000-000000588|PARADIŽNIKOVA OMAKA Z OLIVAMI PESTO, SPAR, 190G|1,57 |   2023020518|
         3|000-000000588|PARADIŽNIKOVA OMAKA Z OLIVAMI PESTO, SPAR, 190G|1,56 |   2023020306|
         4|000-000000588|PARADIŽNIKOVA OMAKA Z OLIVAMI PESTO, SPAR, 190G|1,55 |   2023020304|
         1|000-000001588|BIO GYROS, SPAR VEGGIE, 200G                   |3,39 |   2023020518|
         2|000-000001588|BIO GYROS, SPAR VEGGIE, 200G                   |3,40 |   2023020306|
         1|000-000012278|SIROVA OMAKA, FANT, 30G                        |1,19 |   2023020605|

Od tu naprej pa ne znam spravit predzadnje cene (in datuma) v nov stolpec.

Želel bi takšen izpis:

id           |product_name                                   |last_price|date_hour_key_last|second_to_last_price|date_hour_key_second_to_last|
-------------+-----------------------------------------------+----------+------------------+--------------------+----------------------------+
000-000000588|PARADIŽNIKOVA OMAKA Z OLIVAMI PESTO, SPAR, 190G|1,58      |        2023022613|1,57                |                  2023020518|
000-000001588|BIO GYROS, SPAR VEGGIE, 200G                   |3,39      |        2023020518|3,40                |                  2023020306|
000-000012278|SIROVA OMAKA, FANT, 30G                        |1,19      |        2023020605|                    |                            |

V osnovi rabim samo kakšen sample, in bom poizkusil to notri spravit.

Hvala.

Dela v nulo. Hvala.

Pa ta drugi LEFT JOIN z dense_rank=3 sem odstranil, in vseeno dela. Rabim samo zadnji dve ceni.

Končna verzija:

WITH p AS (
	# zgornji sql ...
)
SELECT p.`id`, p.`product_name`,
	p.`price` AS 'last_price',
	p.`date_hour_key` AS 'date_hour_key_last',
	IF (dr2.`price` IS NULL, 'null_1', dr2.`price`) AS 'second_to_last_price',
	IF (dr2.`date_hour_key` IS NULL, 'null_2', dr2.`date_hour_key`) AS 'date_hour_key_second_to_last'
FROM p
LEFT JOIN p AS dr2 ON dr2.`id` = p.`id` AND dr2.`dense_rank` = 2
WHERE p.`dense_rank`=1;

Ter še sample izpis:

id           |product_name                                                   |last_price|date_hour_key_last|second_to_last_price|date_hour_key_second_to_last|up_down|
-------------+---------------------------------------------------------------+----------+------------------+--------------------+----------------------------+-------+
000-000000013|TEKOČI NAVADNI JOGURT 3,2%, SPAR, 500G                         |0,89      |        2023031610|0,94                |2023030717                  |down   |
000-000000030|TEKOČI NAVADNI JOGURT 1,3%, SPAR, 500G                         |0,89      |        2023031610|0,94                |2023030717                  |down   |
000-000000041|PASIRANA ŠPINAČA SPAR, 350G                                    |0,52      |        2023031610|0,53                |2023030717                  |down   |
000-000000046|JAJCA HLEVSKE REJE BREZ GSO, L, SPAR, 10/1                     |3,11      |        2023031610|2,84                |2023030717                  |up     |
000-000000051|NEPASIRANA POLMASTNA SKUTA, SPAR, 500G                         |2,59      |        2023031610|2,99                |2023030717                  |down   |
000-000000060|JAJCA HLEVSKE REJE, M, SPAR, 10/1                              |3,01      |        2023031610|2,74                |2023030717                  |up     |
000-000000068|KROMPIR ZA PEČENJE SPAR, 2,5KG                                 |3,49      |        2023031610|2,99                |2023030717                  |up     |
000-000000075|BELO VINO RUMENI MUŠKAT, SPAR, 1L                              |3,89      |        2023022613|3,49                |2023020518                  |up     |
000-000000092|VODKA SPAR, 1L                                                 |9,49      |        2023022613|8,99                |2023020518                  |up     |
000-000000094|SIR GAUDA, SPAR, 1KG, (REZANO)                                 |9,29      |        2023022613|10,99               |2023020518                  |up     |
000-000000096|TUNA V OLJČNEM OLJU, SPAR, 3X80G                               |2,99      |        2023022613|2,69                |2023020518                  |up     |
000-000000122|ČOKOLADA V PRAHU, SPAR, 200G                                   |1,69      |        2023022613|1,38                |2023020518                  |up     |
000-000000177|JUŠNE KROGLICE, SPAR, 100G                                     |0,57      |        2023022613|0,47                |2023020518                  |up     |
000-000000186|PELINKOVEC, SPAR, 1L                                           |7,94      |        2023022613|7,49                |2023020518                  |up     |
000-000000192|OKROGLOZRNAT BELI RIŽ SPAR, 1KG                                |2,08      |        2023022613|1,88                |2023020518                  |up     |

A je kakšen poseben razlog, da cene - ki bi jih bilo naravno shranjevati kot števila - shranjuješ kot niz znakov (VARCHAR) in nato vsakič znova pretvarjaš v poizvedbah?

Primerjanje nizov znakov poteka en znak za drugim, zato seveda velja '9' > '10', ker se znak '9' pojavi v naboru znakov kasneje kot znak '1'.