[czlapka]

Nie śmiejcie się ale mam chyba jakieś zaćmienie umysłu i przekombinowuje a to jest chyba dość trywialny problem

Potrzebuję wyliczyć powiedzmy średnią dla różnych produktów ale założenie jest takie, że średnia dla lutego ma być liczona dla produktów z datą sprzedaży z października i listopada, dla marca z datą sprzedaży listopada i grudnia itd. (zawsze brać pełne 3 i 4 miesiąc wstecz).

Docelowy wynik ma być w formie

DATE PRODUCT AVG
2/1/2024 productA 115.56 liczone na danych z października i listopada
2/1/2024 productB 178.87 liczone na danych z października i listopada
2/1/2024 productC 105.23 liczone na danych z października i listopada
1/1/2024 productA 117.67 liczone na danych z listopada i grudnia
1/1/2024 productB 167.23 liczone na danych z listopada i grudnia
1/1/2024 productC 174.56 liczone na danych z listopada i grudnia

Stan obecny mi wychodzi łatwo, bo używam MONTH(CURRENTDATE), YEAR(CURRENTDATE), DATEADD które wylicza 1 dzień miesiąca 4 miesiące temu i ostatni dzień miesiąca 3 miesiące temu.

Ale jak to zrobić łatwo by było uniwersalne i liczyło mi np dla całego 2023 roku?
Chciałem jako CTE gdzie wpierw mam tabelkę z kalendarzem z którego wybieram tylko 1 dni miesiąca(i wyliczam dla niego 1 dzień miesiąca sprzed 4 miesięcy i ostatni dzień miesiąca sprzed 3 miesięcy) i potem joinuje z tabelą z danymi ale coś nie wychodzi.

Pisane w Snowflake jakby coś to zmieniało

#sql #bazydanych

[genco1]

@czlapka nie wiem czy dokładnie o to chodzi, ale może coś nakieruje

SELECT
    CURRENT_DATE(),
    product_name,
    AVG(price)
FROM table_name
WHERE dt >= DATE_TRUNC('month', DATEADD('month', -4, CURRENT_DATE()))
AND dt < DATE_TRUNC('month', DATEADD('month', -2, CURRENT_DATE()))
GROUP BY 1, 2

[czlapka]

@genco1 No właśnie tak mam zrobione ale to wtedy mam wyniki dla dziś a chce dla każdego miesiąca z 2023 roku takie coś mieć. Myślę jeszcze może jakoś liczyć sumy dla miesięcy i funkcje typu LAG? Ale sam nie wiem ;d

[genco1]

@czlapka: możesz pobawić sie ze zmiennymi i pętlami i podstawić za current_date zmienną

[antagonista1111]

@czlapka: nie wiem czy są finkcje okienkowe w snowflake ale "u mnie dziala"

WITH CTE
AS
(SELECT
   ProductName,
   COUNT(cn.ProductName) AS ProductCount,
   YEAR(ProductSale) AS year,
   MONTH(ProductSale) AS month
FROM  Products
GROUP  BY YEAR(ProductSale),
      MONTH(ProductSale),
      ProductName)
SELECT
  ProductName,
  SUM(ProductCount) OVER (PARTITION BY ProductName
              ORDER BY CTE.year ASC,
                   CTE.month ASC
              ROWS BETWEEN 4 PRECEDING AND 3 PRECEDING),
  CTE.year,
  CTE.month,
  CTE.ProductCount
FROM CTE

[Rokuto]

@czlapka: a to nie będzie coś takiego?
Na początku wyliczasz pierwszy dzień każdego miesiąca w roku (łatwiej później obliczyć początek i koniec okresu)
Następnie dla każdego miesiąca obliczasz dwumiesięczne okresy.
A później możesz wykorzystać to do grupowania.

Nie wiem czy wszystko jest obsługiwane w Snowlfake

;with cte_year_month AS (
	SELECT
	DATE_FROM_PARTS(2023, 1, 1) AS start_of_month

	UNION ALL

	SELECT
	DATEADD(day, 1, LAST_DAY(start_of_month)) AS end_of_month
	FROM cte_year_month
	WHERE YEAR(DATEADD(day, 1, LAST_DAY(start_of_month))) < 2023

[czlapka]

@antagonista1111: Dzięki! Nie do końca w sumie poprawnie opisałem przypadek biznesowy ale wygląda na to, że ten sposób działa ;)

@Rokuto Też dzięki ;) Tak właśnie też próbowałem wczoraj ale coś nie wychodziło tak jak powinno ale może mój błąd

[antagonista1111]

@czlapka: podziel sobie wynik przez 2, bo nie zauwazylem ze tam ma byc srednia. Jak to zrobisz to pojawi sie problem z jednym wierszem bo 3 najwczesniejszy miesiac po zsumowanie 1 najwczesniejszego miesiaca i NULLA jak podzielisz przez 2 to wyjdzie troche zle, ale ogolnie sposob dziala

[czlapka]

@antagonista1111 Ogólnie to tam mają być tak naprawdę średnie z sum dwóch kolumn więc kod wygląda inaczej ale sposób działa ;)

[antagonista1111]

@czlapka: Musi dzialac, nie ma innej opcji :D