Problem z ostatnią datą w danym tygodniu/miesiącu (PostgreSQL)

0

Witam

Oprócz tabeli danych klientów, których windykuję, mam drugą prostą tablicę (w Postgresie), która zapisuje statusy tych klientów w czasie (np. aktywny/nieaktywny) kilka razy w ciągu tygodnia na życzenie użytkownika. W związku z tym, że muszę przygotowywać zestawienia cyklicznie tygodniowo/miesięcznie wraz z informacją, czy dany klient jest aktywny, czy nie, potrzebuję pomocy w napisaniu selecta, który pokazywałby dane klientów z pierwszej tabeli i zestawił ich statusy w ujęciu - koniec zeszłego tygodnia (-1), koniec tygodnia wcześniejszego(-2) i analogicznie odnośnie miesięcy. Przykładowe tablice:

  1. public.klienci (tabela pokazująca aktualny stan klienta, czy ma aktywną umowę czy nie):
idklienta	      klient	  status
50	              Firma A	  Nieaktywny
74	              Firma B	  Nieaktywny
85	              Firma C	  Aktywny
109	              Firma D	  Aktywny
  1. public.statusy_historia:
id	idlienta   datastatusu   status
1		 50	2021-01-21	Aktywny
2		 74	2021-01-21	Nieaktywny
3		 85	2021-01-21	Aktywny
4	    109	2021-01-21	Aktywny
230	     50	2021-02-21	Aktywny
231	     74	2021-02-21	Nieaktywny
232	     85	2021-02-21	Aktywny
233	    109	2021-02-21	Aktywny
459	    50	2021-03-21	Aktywny
460	    74	2021-03-21	Nieaktywny
461	    85	2021-03-21	Aktywny
462	    109	2021-03-21	Aktywny
68	    50	2021-04-21	Aktywny
689	    74	2021-04-21	Nieaktywny
690	    85	2021-04-21	Aktywny
691	    109	2021-04-21	Aktywny
917	    50	2021-05-03	Nieaktywny
918	    74	2021-05-03	Nieaktywny
919	    85	2021-05-03	Aktywny
920	    109	2021-05-03	Aktywny
1146	50	2021-05-10	Nieaktywny
1147	74	2021-05-10	Nieaktywny
1148	85	2021-05-10	Aktywny
1149	109	2021-05-10	Aktywny
1375	50	2021-05-16	Nieaktywny
1376	74	2021-05-16	Nieaktywny
1377	85	2021-05-16	Aktywny
1378	109	2021-05-16	Aktywny
1604	50	2021-05-18	Nieaktywny
1605	74	2021-05-18	Nieaktywny
1606	85	2021-05-18	Aktywny
1607	109	2021-05-18	Aktywny
1833	50	2021-05-21	Nieaktywny
1834	74	2021-05-21	Nieaktywny
1835	85	2021-05-21	Aktywny
1836	109	2021-05-21	Aktywny

gdzie 1 kolumna "id" to po prostu kolejny numer z sekwencji.
To co chcę uzyskać to 2 warianty tabeli klientów pokazujące dane poprzednich statusów.
Pierwszy pokazujący jaki miał status w zeszłym tygodniu i 2 tygodnie temu (status wzięty z ostatniej daty z danego tygodnia/m-ca) czyli:

idklienta	klient   tydzień(-2)    tydzień (-1)    status_aktualny
50	      Firma A	       ?	      ?	           Nieaktywny
74	      Firma B	       ?	      ?	           Nieaktywny
85	      Firma C	       ?	      ?	           Aktywny
109	      Firma D	       ?	      ?	           Aktywny

gdzie w znakach zapytania powinny pojawić się statusy odpowiadające najnowszej dacie z poprzedniego tygodnia, tygodnia (-2)
Drugi wariant w przypadku miesięcy analogicznie po najnowszej dacie w tabeli statusy_historia dotyczącej poprzedniego miesiąca, miesiąca (-2), czyli:

idklienta	klient   miesiąc(-2)    miesiąc (-1)    status_aktualny
50	      Firma A	       ?	      ?	           Nieaktywny
74	      Firma B	       ?	      ?	           Nieaktywny
85	      Firma C	       ?	      ?	           Aktywny
109	      Firma D	       ?	      ?	           Aktywny

Próbowałem już z numerami tygodni poprzez klauzule takie jak:

tydzien >= (extract('week' from current_date)-1

ale zwracało mi błąd typu:

BŁĄD: ponad jeden wiersz zwrócony przez podzapytanie użyte jako wyrażenie
Stan SQL: 21000

Select mój obecnie wygląda tak:

SELECT idklienta,
  klient,
--w tym miejscu powinien się znaleźć select odwołujący się do bazy statusów i pokazujący status (-2) tygodnia lub miesiąca zależnie od wariantu,
--w tym miejscu powinien się znaleźć select odwołujący się do bazy statusów i pokazujący status (-1) tygodnia lub miesiąca zależnie od wariantu,
  status_aktualny

  FROM klienci k ORDER BY k.idklienta

Nie posiadam zaawansowanej wiedzy odnośnie SQL'a i potrzebowałbym pomocy. Co powinienem wstawić w miejsca komentarzy powyższego selecta, żeby "łapało" mi ostatni status z wcześniejszych tygodni/miesięcy?

Zapewne dla wielu Forumowiczów to zadanie ze szkoły ale nie potrafię sobie z tym poradzić i będę wdzięczny za pomoc. Z góry dziękuję

1

Potrzebujesz określić stan klienta na koniec każdego tygodnia, a potem wziąć tylko dla tych o jeden i dwa do tyłu. Najpierw musisz pogrupować po numerze tygodnia (jak poprzedni tydzień oznacza poprzedni tydzień w sensie od poniedziałku do niedzieli, a nie 7 dni wstecz), Potem musisz wziąć ostatni wpis dla każdego tygodnia. Prawdopodobnie konieczne będzie wykorzystanie funkcji https://www.postgresqltutorial.com/postgresql-last_value-function/ - nie jest to trywialne i raczej ciężko będzie ci to zrobić bez wiedzy z SQLa.

0

Coś w tym stylu może zadziałać, ale robiłem to na szybko, nie wiem czy będzie dobrze, i są LEFT JOINY więc dane mogą się powtarzać - może pomóc dodanie DISTINCT(klienci.idklienta)

WITH tydzien_temu AS (
 SELECT * FROM statusy_historia WHERE datastatusu - INTERVAL 1 WEEK <= date(now()) AND datastatusu - INTERVAL 2 WEEK > date(now()) ORDER BY datastatusu DESC
)

WITH dwa_tygodnie_temu AS (
 SELECT * FROM statusy_historia WHERE datastatusu - INTERVAL 2 WEEK <= date(now()) AND datastatusu - INTERVAL 3 WEEK > date(now()) ORDER BY datastatusu DESC
)

SELECT klienci.*, tydzien_temu.status as status_tydzien_temu, dwa_tygodnie_temu.status as status_dwa_tygodnie_temu 
FROM klienci
LEFT JOIN tydzien_temu ON tydzien_temu.idklienta = klienci.idklienta
LEFT JOIN dwa_tygodnie_temu ON dwa_tygodnie_temu.idklienta = klienci.idklienta
4 

with k as
(
select 
	k.*, 
	max(case when datastatusu< current_date-(extract(dow from current_date))*interval'1d' then id end) tydzien_1, 
	max(case when datastatusu< current_date-'1w'::interval-(extract(dow from current_date))*interval'1d' then id end) tydzien_2
from 
	klienci k
	left join 
	statusy_historia sh 
	on k.idklienta=sh.idklienta and sh.datastatusu<=current_date
group by k.idklienta,k.klient,k.status
)
select k.idklienta, k.klient, k.status, sh1.status, sh2.status
from 
	k
	left join
	statusy_historia sh1
	on k.tydzien_1=sh1.id
	left join
	statusy_historia sh2
	on k.tydzien_2=sh2.id
0

@Marcin.Miga: Mistrzowskie rozwiązanie! Serdecznie dziękuję za pomoc! O to właśnie chodziło!
Dla tygodni chodzi super. Proszę mnie poprawić lub potwierdzić, że jest ok - Jeśli się mylę, w przypadku miesięcy, klauzulę:

max(case when datastatusu< current_date-(extract(dow from current_date))*interval'1d' then id end) tydzien_1, 
max(case when datastatusu< current_date-'1w'::interval-(extract(dow from current_date))*interval'1d' then id end) tydzien_2

wystarczy wg mnie zmienić na:

max(case when datastatusu< current_date-(extract(day from current_date))*interval'1d' then id end) miesiac_1, 
max(case when datastatusu< current_date-'1month'::interval-(extract(day from current_date))*interval'1d' then id end) miesiac_2

Sprawdziłem na testowych danych i wydaje się, że to działa, ale chciałbym potwierdzić, że dobrze zbudowałem wariant dla miesięcy
Pozdrawiam serdecznie!

1 użytkowników online, w tym zalogowanych: 0, gości: 1