Hi,
W bazie zapisuje rekordy które mają szescioznakowy sequence number (od 000001 do 999999). Z bazy muszę wybrać zawsze najstarszy rekord, czyli o najniższym sequence number. Jak to zrobić w przypadku przekręcenia licznika (z 999999 na 000001). Przy zapisywaniu rekordów rejestruje też czas, ale może tak byc ze rekord 000001 przyjdzie kilka milisecund przed 999999.
Pozdrawiam Usjwo
Albo ja jeszcze spie albo najstarszy to zawsze będzie 0000001 - ewentualnie jeśli się licznik przekręcił to jeśli rejestrujesz tez datę w kolejnej kolumnie to bierzesz najstarszą + 0000001 right ? Czy ja czegoś tu nie widzę :|
Rekord to String(120), w środku jest 6 znaków które tworzą sequence number.
W bazie nie będzie więcej rekordów jak 10000, starsze są kasowane.
Myslłem żeby najpierw zapytać o rekord najmniejszy od 990000 do 999999, jak nie ma to z najmniejszym numerem. Można to za pomocą jednego SELECTA?
Przykład:
mam takie rekordy w bazie w kolejnosci rejestracji:
999997
000001
999998
999999
000002
Potrzebuje pobrac w kolejności 999997, 999998, 999999, 000001, 000002
Baza danych to MS SQL Expres
Daj DDL tabeli, podaj jaki DBMS, daj przykładowe dane wejściowe i oczekiwany wynik.
Edytowałem powyżej
Nie mam teraz dostępu do programu, więc tak z pamieci.
Odbieram z servera 120 bytow danych. Zapisuje to do MS SQL Expres o nastepującej strukturze:
Data - nchar(120) - Dane
InputTime - DateTime - czas odebrania rekordu
OutputTime - DateTime - czas wysłania rekordu
Used - bool (true - jeżeli dane zostały wysłane)
Sequence number zaczyna się do 10 bajtu, 6 znaków. W bazie trzymam tylko ostatnie 10000 rekordów.
Sekwencja odbierania niekoniecznie pokrywa się odbieraniem rekordów.
Przykład:
Odbieram rekordy w następującej sekwencji:
999997
000001
999998
999999
000002
Potrzebuje pobrac i wysłać w kolejności
999997
999998
999999
000001
000002
Genialna tabela bez klucza pierwotnego (sarkazm, dodam dla jasności)
Ty to projektowałeś? To było za wcześnie ...
Domyślam się że znasz rozwiązanie, więc jak mógłbym prosić?
Usjwo napisał(a):
Domyślam się że znasz rozwiązanie, więc jak mógłbym prosić?
Mam wrażenie, że to przekombinowane ... no ale coś w ten deseń chyba trzeb by iść: http://sqlfiddle.com/#!18/5f336/24
SELECT * FROM (
SELECT 1 ord, Seq FROM test
WHERE Seq>=(
SELECT MAX(t1.Seq)
FROM test t1
LEFT JOIN test t2
ON CAST(t1.seq as int) -1 = CAST(t2.seq as int)
WHERE t2.seq is null)
UNION
SELECT 2 ord, Seq FROM test
WHERE Seq<(
SELECT MAX(t1.Seq)
FROM test t1
LEFT JOIN test t2
ON CAST(t1.seq as int) -1 = CAST(t2.seq as int)
WHERE t2.seq is null)
) t
ORDER by ord asc
BlackBad - Twój kod zdaje się nie działać na moich danych.
Poczyniłem swój, który u mnie działa, ale jakoś nie wiem, czy to będzie specjalnie optymalne (testowałem na jedynie kilkunastu rekordach)
WITH cte
AS (
SELECT cast(min(substring(a, 2, 6)) AS INT) mi,
cast(max(substring(a, 2, 6)) AS INT) ma
FROM tab
)
SELECT tab.*
FROM tab
CROSS APPLY cte
ORDER BY iif(cte.ma - cte.mi > 90000 AND substring(tab.a, 2, 1) = '0', 1, 0),
substring(tab.a, 2, 6)
Oczywiście do podmiany nazwy tabel i kolumn, a także znak początkowy w substringach.
SELECT A1.dane, substr(a1.dane,10,6) AS 'liczba'
FROM A1
ORDER BY sign(500000-substr(a1.dane,10,6)) asc, substr(a1.dane,10,6) asc
(być może nie substr, a substring)
Wynik z Excel:
EDIT: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=eaa5eeef04042899c8cd66f3d7f1c6b2
Fac napisał(a):
Poczyniłem swój, który u mnie działa, ale jakoś nie wiem, czy to będzie specjalnie optymalne (testowałem na jedynie kilkunastu rekordach)
max po substringu nigdy nie będzie optymalne. Potrzebujesz oddzielnych kolumn i indeksów. To nie Excell, a relacyjna baza danych, trzeba do tego się przygotować.
PROJEKT JEST SKOPANY
Wróć do designu tej tabeli, zły design tu tworzy problem, i pewnie nie jest to ostatni. Z dobrym designem kwerenda stanie naturalna, a nie jakieś tricki itd...
A koledzy "pomagający" betonują zły projekt, zamiast go wyprowadzić na prostą. Udzielacie obecnie odpowiedzi na pytanie "jak jeść zupę widelcem"
AnyKtokolwiek napisał(a):
Fac napisał(a):
Poczyniłem swój, który u mnie działa, ale jakoś nie wiem, czy to będzie specjalnie optymalne (testowałem na jedynie kilkunastu rekordach)
max po substringu nigdy nie będzie optymalne. Potrzebujesz oddzielnych kolumn i indeksów. To nie Excell, a relacyjna baza danych, trzeba do tego się przygotować.
PROJEKT JEST SKOPANY
Wróć do designu tej tabeli, zły design tu tworzy problem, i pewnie nie jest to pierwszy.
I koledzy betonują zły projekt, zamiast go wyprowadzić na prostą.
A może byś w takim razie naprowadził, zamiast tylko komentować, że jest źle?
Owszem, miło byłoby mieć osobną kolumnę, w której przechowywany byłby tylko i wyłącznie ten konkretny numer, po którym chcemy posortować, najlepiej w formie liczbowej. ALE! Zmieniłaby się tylko wydajność odczytu, kosztem nadmiarowości danych oraz wydłużonego czasu zapisu. I w niektórych przypadkach ten koszt byłby uzasadniony, ale jeśli mamy zamiar odpytywać bazę raz na dobę (gdzie pojedyncze zapytanie wykonuje się kilkadziesiąt milisekund), a zapisy tworzą się co chwilę, być może nawet co kilka milisekund, to śmiem twierdzić, że przepisywanie danych do kolejnych kolumn z konwersją podczas zapisu może się nie opłacać.
Jednak, bez względu na sposób przechowywania danych (w środku łańcucha znaków, czy w osobnej kolumnie liczbowej), problem sortowania jest ten sam, a rozwiązanie różnić się będzie jedynie zastosowaniem substringów i konwersji...
Chyba, że czegoś nie wiemy i rozwiązanie jest prostsze - oświeć nas.
A mógłbyś napisać konkretnie, co jest złe? Baza danych już istnieje, ale mogę napisać program który przepisze do nowej struktury.
Wszystkie dane podałem, wiec o ile to nie tajemnica to podaj najlepsze rozwiązanie. Rekordów nie jest dużo wiec najwyżej użyje dwóch SELECTOW, i Rez będzie działać. Ale jak może być lepiej, to z chęcią posłucham.
To żeby uściślić problem. Normalnie dane przychodzą i są wysyłane z częstotliwością kilku minut. Ale jeżeli program nie działa przez jakiś czas, to po połączeniu server przepycha od razu cała brakująca zawartość, Wtedy zdaza się zaburzenie sekwencji i interwał jest msekundowy. Wypisywanie rekordów jest zawsze długie.
Kolega @AnyKtokolwiek sensownie pisze. Na zbiorze,,który jest konstruowany w sposób opisany ciężko zdefiniować porządek. Jakie gwarancję kolejności odbioru/zapisu tych sekwencji masz? Jeśli żadne to niby w oparciu o co można porządek definiować?
Ku ...a koledzy @Usjwo i @Fac Zadaniem forum nie jest kopiowanie bazowej wiedzy, którą każdy zajmujący się zagadnieniami musi posiąść, zanim zacznie tworzyć, a nie daj borze coś pachnącego "produkcją"
Usjwo napisał(a):
A mógłbyś napisać konkretnie, co jest złe? Baza danych już istnieje, ale mogę napisać program który przepisze do nowej struktury.
Da się przenieść ambitniejszą kwerendą coś w rodzaju insert ...from ... to czy jak to się pisze w tym dialekcie.
Więcej roboty w tym miejscu, gdzie przyjmujesz dane.
Fac napisał(a):
AnyKtokolwiek napisał(a):
A może byś w takim razie naprowadził, zamiast tylko komentować, że jest źle?
Owszem, miło byłoby mieć osobną kolumnę, w której przechowywany byłby tylko i wyłącznie ten konkretny numer, po którym chcemy posortować, najlepiej w formie liczbowej. ALE! Zmieniłaby się tylko wydajność odczytu, kosztem nadmiarowości danych oraz wydłużonego czasu zapisu. I w niektórych przypadkach ten koszt byłby uzasadniony, ale jeśli mamy zamiar odpytywać bazę raz na dobę (gdzie pojedyncze zapytanie wykonuje się kilkadziesiąt milisekund), a zapisy tworzą się co chwilę, być może nawet co kilka milisekund, to śmiem twierdzić, że przepisywanie danych do kolejnych kolumn z konwersją podczas zapisu może się nie opłacać.Jednak, bez względu na sposób przechowywania danych (w środku łańcucha znaków, czy w osobnej kolumnie liczbowej), problem sortowania jest ten sam, a rozwiązanie różnić się będzie jedynie zastosowaniem substringów i konwersji...
W pierwszym akapicie prezentujesz jako zawodowy "wydajnościowiec" (skądinąd masz jakieś pomiary podobnej sytuacji, czy mniemasz?)czy DB architekt, by po drugim to wrażenie prysło
PS. dlaczego w formie liczbowej? W czym lepsza? A może gorsza?
@Usjwo Nikt za ciebie nie zaprojektuje te "dobrej" tabeli, bo tylko ty wiesz, co w niej "naprawdę" jest. Każdy element z tych 120 znaków, trzeba zwyczajnie, zdroworozsądkowo nazwać w postaci kolumny. Dyskutowany fragment "SeqNumber", ale to co przed nim i za nim też.
Do tej pory uchylasz się od pytania "podaj przykład danych"
Na zapis powiedzmy ze nie mam wpływu. Wysyłanie musi być tylko według sequence number. Dodatkowo między zapisaniem rekordy a jego wypisaniem upływają godziny. Nie jest tak ze jest zapis do bazy i od razu wypis. Wiec te wypisywane rekordy są stabilne.
Mogę sprawdzić rekordy w zakresie 990000 do 9999999 i od 000001 do 009999, jeżeli są to jest zapętlenie sequence number i wiem gdzie szukać.
Chciałem to zrobić za pomocą jednego zapytania do bazy.
Usjwo napisał(a):
Na zapis powiedzmy ze nie mam wpływu. Wysyłanie musi być tylko według sequence number. Dodatkowo między zapisaniem rekordy a jego wypisaniem upływają godziny. Nie jest tak ze jest zapis do bazy i od razu wypis. Wiec te wypisywane rekordy są stabilne.
Mogę sprawdzić rekordy w zakresie 990000 do 9999999 i od 000001 do 009999, jeżeli są to jest zapętlenie sequence number i wiem gdzie szukać.
Chciałem to zrobić za pomocą jednego zapytania do bazy.
To "powiedzmy" że masz.
Sorry, ale dodanie ID nie zrywa żadnej kompatybilności po stronie pompującego - w każdej racjonalnej sytuacji
Disclaimer: nie wiem jakie inne "mądrości" tam są.
Mam dosc tego wątku.
Do tej pory uchylasz się od pytania "podaj przykład danych"
Ale przecież podałem, dane otrzymane to 120 Bytów (ASCII), w środku danych jest 6 bytowy sequence number. Po połączeniu server przesyła mi dane z rożna częstotliwością i sequence number niekoniecznie jest zachowany.
Zapisuje to do bazy i na zapytanie od innej maszyny wysyłam najstarszy rekord (według sequence number).
Aplikacja jest w C#. To chyba wszystko, już jaśniej chyba nie potrafię. Mogę dodać kolumnę unique ID z sequence number, tylko dalej nie widzę rozwiązania problemu. Załóżmy ze mogę dowolnie ta bazę przebudować, żeby było optymalnie.
Nigdy nie nazywałem siebie ekspertem. Jeśli sprawiłem takie wrażenie, najuprzejmiej Cię za to przepraszam.
Tak czy inaczej - został przedstawiony konkretny problem, a Ty odpowiedziałeś w stylu "nie znasz się na bazach, masz tu link do encyklopedii, poczytaj sobie, czym są bazy relacyjne". Rozumiem, że uważasz, że tabela jest źle zaprojektowana. Jeśli jednak rzucasz takie hasło, to podpowiedz, jak być powinna. Sam jestem ciekaw i to bez żadnych złośliwości ciekaw - a nuż się czegoś nauczę. Ponieważ jednak ktoś zarzucał autorowi, że nie podał przykładowych danych, wymyślę je sam:
AY126FA65S999998UJU580015UK
AX125EA79C999997LJI7886646UL
AX125FA78B000002KJI7886546UL
AX125EA79C000001LJI7886646UL
AY126FA65S999999UJU580015UK
BZ126FA65S000003UJU580015UK
Mój zmyślony przykład (analogiczny do pierwotnego) jest następujący:
Z systemu zewnętrznego dostaję dwudziestoośmioznakowe łańcuchy, w których znaki między 11 a 16 stanowią numer kolejny wpisu generowany przez system zewnętrzny (SEQ), przy czym po przekroczeniu liczby 999999 licznik się zeruje niczym w starym samochodzie i zaczyna kręcić od 000001. Poszczególne rekordy mogą wpadać do bazy w zaburzonej kolejności, co widać powyżej. Istnieje potrzeba, aby w bazie przechowywać wyłącznie ostatnie 10000 rekordów.
Podstawowy problem - jak wyświetlić wszystkie rekordy sortując je po SEQ z zastrzeżeniem, że jeśli w bazie istnieją jednocześnie rekordy, które "przekręciły licznik", to w pierwszej kolejności mają zostać wyświetlone te sprzed wyzerowania licznika, a dopiero później te po wyzerowaniu. Pozostałe dane znajdujące się w ciągu znaków nie mają żadnego znaczenia dla sortowania.
Z powyższego przykładu wyszłoby nam coś takiego:
AX125EA79C999997LJI7886646UL
AY126FA65S999998UJU580015UK
AY126FA65S999999UJU580015UK
AX125EA79C000001LJI7886646UL
AX125FA78B000002KJI7886546UL
BZ126FA65S000003UJU580015UK
PYTANIE:
Jak stworzyć zapytanie, by wyświetlić dane w pożądany sposób, ewentualnie jak przechowywać dane w bazie, by było to łatwiejsze?
Tak naprawdę to z tego co opisujesz to ani numer sekwencji, ani data zapisu nie daje Ci informacji o tym który rekord jest najstarszy, więc najprościej byłoby olać te niedogności i pobrać najstarszy rekord tak:
select top 1 * from tabela order by data_zapisu desc
Drugi sposób to jeżeli przyjmiemy założenie, że masz każdy numer sekwencji po kolei bez luk, to pobbrać ten który nie ma poprzednika, możesz użyć funkcji lag
Trzeci sposób to: sprawdzić czy różnica pomiędzy najmniejszym a najwiekszym jest wieksza niż 10000 (bo tyle masz rekordów) i zaleznie od tego wyliczyć numer sekwencji najstarszego
Co wyróżnia rekord seq=1 po resecie sekwencji od rekordu seq=2 sprzed resetu i rekordu seq=9999 sprzed resetu sekwencji, ale zapisanego po rekordzie z seq=1?
Może dodatkowy atrybut uzupełniany przy insercie? Atrybut przechowywalby nr kolejnego resetu. Wówczas sortowanie po reset_id, seq powinno załatwić sprawę. Wyliczanie reset_id w aplikacji bądź triggerem.
Panczo napisał(a):
Trzeci sposób to: sprawdzić czy różnica pomiędzy najmniejszym a najwiekszym jest wieksza niż 10000 (bo tyle masz rekordów) i zaleznie od tego wyliczyć numer sekwencji najstarszego
To chyba jak na razie najlepszy/najprostszy spsób. Pobieram największy i najmniejszy rekord. Jeżeli róznica między nimi jest < 10000, to ten najmniejszy jest najstarszy, jeżeli nie to pobieram najmniejszy z zakresu najstarszy-10000.
Teraz już mam komputer to mogę napisać więcej. Jakkolwiek powyższe rozwiązanie powinno zdziałać, to z chęcią nauczę się jak zrobić to poprawnie, wraz z projektem bazy danych.
Troche backgroundu.
Program działa juz u kilku klientów. U wszystkich odbieranie rekordów ma ten sam mechanizm (co najwyżej protokoły inne). Dane przychodzą co kilka minut, ale jak aplikacja była wyłaczona to server wysyła brakujące dane po nawiązaniu komunikacji. Rózne za to są kryteria wysyłania rekordów. Nie zawsze jest to sequence number. Jak dotychczas były takie kryteria:
To czy stworze osobną kolumne na sequence number i zaminie to na integer i ustawie jako klucz to sprawa wtórna, co najwyżej zredukuje czas odczytu, który nie jest krytyczny.
Jakby kolega AnyKtokolwiek lub any ktokolwiek inny, mógł podać takie rozwiązanie, to byłbym wdzięczny. Bez żadnych zgryźliwości, juz jest po wyborach. :D
Baza danych służy tylko jako taki większy buffor i łatwiejszy w obsłudze. Tym bardziej że na maszynach na których to działa MS SQL już istnieje. Mógłbym napisac własny buffor i ograniczyć ilośc recordów do 1000 i pewnie też by działało, tylko po co wywżać otwarte drzwi. Także minusy "mojej" bazy są jak najbardziej mile widziane.
Fac napisał(a):
Ale może powiedz, czy rozwiązania moje i @BlackBad spełniają założenia? Bo wg. mnie spełniają...
Przeanalizuje i wypróbuje. Jak pisałem z bazami maiłem bliski kontakt 30 lat temu, a juz pamięc nie ta, żeby pamietac wszyskie niuanse SELECTa
Twój opis pasuje mi do rozwiazania kolejkowania MQ
https://pl.m.wikipedia.org/wiki/Kolejka_komunikat%C3%B3w
RabbitMQ, Kafka
Damian Korczowski napisał(a):
Twój opis pasuje mi do rozwiazania kolejkowania MQ
Muszę zmienić nazwę applicacji na UsjwoMQ ;)
UsjwoMQ with Priority Exceptions - marketingowo dobrze brzmi
Ja tu czegoś nie rozumiem, piszesz o tabeli na 10000 rekordów, to nie jest jakoś specjalnie dużo, nawet dla wersji express. Czy obawa przed wydłużeniem czasu zapisu nie jest zbyt na wyrost?
Jeżeli zapis odbywa się pojedynczymi insertami, to wystarczy kolumna identity, którą w łatwy sposób można będzie wykorzystać do pobrania najstarszego. Jeżeli faktycznie numer sekwencji nie wynika z kolejności dodawania to możesz użyć kolumny wyliczeniowego i w niej przechowywać numer sekwencji. To nie jest konieczne ale zdecydowanie ułatwia sprawę i nie wymaga zmian w kodzie dodającym dane...
Ty ten najstarszy numer potrzebujesz w jakim celu?
Panczo napisał(a):
Ja tu czegoś nie rozumiem, piszesz o tabeli na 10000 rekordów, to nie jest jakoś specjalnie dużo, nawet dla wersji express. Czy obawa przed wydłużeniem czasu zapisu nie jest zbyt na wyrost?
Ale ja sie nie obawiam czasu zapisu. bo to działa całkiem dobrze. To poboczny topik, zrodził się przy dyskusji. Zapis może byc w cyklu ms, w porównaniu z wypisem który jest w cyklu sekundowym.
Panczo napisał(a):
To nie jest konieczne ale zdecydowanie ułatwia sprawę i nie wymaga zmian w kodzie dodającym dane...
Pełna zgoda tylko to nie jest rozwiązanie problemu, a raczej optymalizacja
Panczo napisał(a):
Ty ten najstarszy numer potrzebujesz w jakim celu?
Musze wysłać dane o najmniejszym sequence number. Problem pojawia się po przekreceniu licznika. Potrafię to znależć odpowiedni rekord (przez dwa lub trzy zapytania).
Chciałem zapytać czy jest jakiś sposób żeby to ogarnąc w jednym zaytaniu jako że moja znajomość SQL jest niewielka, zapytałem tutaj.
Rozwiązanie idą w stonę przebudowy bazy danych, a tego bym chciał uniknąć.
AnyKtokolwiek stwierdził że baza jest do okręznicy, więc zapytałem jak powinno to wyglądać z punktu widzenia specjalisty, po to by nie popełniać błedów.
Z takiego codu:
min = SELECT TOP 1 Data FROM Tabela ORDER BY Seq_No ASC)
max = SELECT TOP 1 Data FROM Tabela ORDER BY Seq_No DSC)
if (Seq_no(max) -Seq_no(min)) < 10000 {wyslij = min}
else
{wyslij = SELECT TOP 1 Data FROM Tabela ORDER BY Seq_No ASC WHERE Seq_No <= Seq_no(max) AND Seq_No > Seq_no(max) - 10000}
Seq_no - wyciąga sequence number ze stringu
zrobić taki:
wyslij = SELECT....
Tylko prosze bez komentarzy że w drugiej lini nie ma średnika ;)
Można:
select
case when max(seq_no)-min(seq_no) > 20000 then
min(case when seq_no<20000 then 999999 else seq_no end)
else
min(seq_no)
end last_no
from (
select cast(substring(seq,11,6) as int) seq_no from tb) t
http://sqlfiddle.com/#!18/dd168/6
UPDATE, celowo sprawdzam różnicę na 20000, w razie jakby sekwencja się nie zapisała i róznica była większa od załazonoych 10000