Mozgalica: Formatiranje celih brojeva vecih od nule

[ Zidar @ 16.04.2010. 16:33 ] @

Data je tabela koja sadrzi cele brojeve (integer) vece od nule. Na primer:

Treba napisati kveri koji formatira brojeve tako sto dodaje nule na pocetku, ali ne vise nula nego sto je potrebno.

Rezultat kverija treba da izgleda ovako:

Code:

       Broj BrojFMT
----------- -------
          9 009
          1 001
        100 100
         86 086
         10 010
         94 094
        195 195
         99 099
          6 006
         52 052

(10 row(s) affected)

U cemu je fora sa ovom mozgalicom? Kveri treba da formatira brojeve tako da je broj nula minimalan. Ovo znaci da broj nula zavisi od broja cifara najveceg broja u tabeli. Ako je najveci broj trocifren, rezultat, kolona BrojFMT treba da ima tacno 3 karaktera. Ako je najveci broj sa 9 (devet cifara), onda rezultat treba formatirati na 9 znakova. Ako je najveci broj dvocifren, trazi se BrojFMT duzne dva znaka.

Ako sada dodam jeden sestocifreni broj, 999999, treba da mi sve bude formatirano na 6 cifara:

Code:

INSERT INTO #Brojevi (Broj) VALUES (999999);

-- Novi rezultat:
       Broj BrojFMT
----------- -------
          9 000009
          1 000001
        100 000100
         86 000086
         10 000010
         94 000094
        195 000195
         99 000099
          6 000006
         52 000052
     999999 999999

(11 row(s) affected)

Sve je dozvoljeno, cak i kursori, mada se ne smatraju dobrim resenjem.

Ako neko brzo resi ovo, jer je mozda lako, samo ja nevidim prosto resenje, prosirite problem tako da se ukljuce i nule. Ovom mozgalicom, trazi se resenje samo za brojeve vece od nule. Ja imam lepo resenje za vece od nule, nulu i negativne nisam probao. Kao ni decimale. U mom resenju, uvodjenje nule komplikuje stvar vise nego uvodjenje negativnih brojeva. Negativni brojevi , nule i decimalesu moguca prosirenja zadatka.

Ko kaze da u SQL nema sta da se istrazuje i resava? Mozda je sve ovo trivijalno u nekomORACLE ili PostGres ili Firebird, ko zna, cilj je da saznamo nesto novo i da se zabavimo.

Na posao :-)

[ Zoran.Eremija @ 16.04.2010. 20:41 ] @

Ajde da razbijem led... Sto bi moja baba rekla da prospem vodu...

[ misk0 @ 16.04.2010. 21:14 ] @

Evo na brzinu jedno Oracle rjesenje, mada bi trebalo raditi na svim DBovima jer je standardan SQL.

U sustini nije mudro tj optimizovano jer ako postoji 10000 redova bice i 10000 nested query-ja ali rekoh da je na brzinu :)

Code (plsql):

SELECT salary, LPAD(salary,

(SELECT MAX(LENGTH(salary)) FROM employees)

,'0') 

FROM employees

Code:

7400                   07400
7300                   07300
6100                   06100
11000                  11000
8800                   08800
8600                   08600
8400                   08400
7000                   07000
6200                   06200
3200                   03200

[ Zidar @ 16.04.2010. 22:07 ] @

@Zoran: Priznajem da nisam ocekivao resenje u Accesu, ali zasto da ne. U rukama MAndusica Vuka bice svaka puska ubojita :-)
U Accesu stvari resavamo sitemom kverija. Prvo jedan kveri pripremi podatke:

Code:

query QDuzinaMax:
SELECT MAX(Len(Trim(Str([Broj]))))                AS duzina,
       MAX(Val("1" & Trim(Str([Broj]))) - [Broj]) AS brojizveden
FROM   broj;

onda drugi kveri iskoristi pripremljene medjurezultate, ovako:

Code:

SELECT broj.broj,
       RIGHT(Str(Dlookup("[BrojIzveden]", "QDuzinaMax") + [Broj])
                , Dlookup("[Duzina]"
                , "QDuzinaMax")) AS brojfmt
FROM   broj;

Verovatno bi ovi Dlookup mogli da se zamene JOINom, ovako:

Code:

SELECT Broj.Broj, Right(Str([BrojIzveden]+[Broj]),[Duzina]) AS BrojFmt
FROM QDuzinaMax, Broj;

Ocekivao sam da ce izbacivanje Dlookup ubrzati kveri, ali je verzija sa Dlookup bila podjednako brza i na 10000 redova. Zakacio sam primer.

@Misko: Nismo trazili efikasno, trazili smo da radi. A mislim da nije sporo, jer query optimizer bi trebao da bude dovoljno pametan da shvati da je ovo skalarni non-correlated subquery i da ga izracuna samo jednom. A mogla bi verovatno cela stvar da se napise kao JOIN (ukoliko ORACLE podrzava in-line izraze):

Code:

SELECT
E.Salary, LPAD(E.Salary, Q.MaxLen,'0')
FROM Employees AS E
INNER JOIN (SELECT MAX(LENGTH(salary)) AS MaxLen FROM employees) AS Q
ON 1=1

Evo dakle dva resenja. Oba imaju slicnu logiku - utvrdi se prvo maksimalna duzina stringa, pa se doda potreban broj nula. Resenje u Accesu dodaje sve nule, pa odsece s desne strane stringa da se dobije trazena duzina. ORACLE ima ponovo olaksicu - LPAD, dodavanje odredjenog broja znakova s leve strane.

Access resenje lepo radi i zanulu, ali za negativne brojeve ne radi tacno. Za Miskovo resenje nemam ORACLE pa ne mogu da proveri, Misko moze da nam javi sta se desava kad se uvede nula ili negativni brojevi.

Zoran i Misko sada mogu da prosire zadatak. I ja sam u medjuvremenu resio problem sa nulom i negativnim brojevima, u MS SQL, donekle slicno Zoranovom resenju. Ako do ponedeljka ne bude novih resenja, zakacicu i moje.

Ima li jos resenja?

[ Shadowed @ 16.04.2010. 22:23 ] @

Mozda lupam, ali ovo misk0-vo bi mozda moglo i ovako (ili ovako nekako):

Code (plsql):

SELECT salary, LPAD(salary,

LENGTH(SELECT MAX(salary) FROM employees)

,'0') 

FROM employees

Poenta je da nadje maksimalni broj pa onda uzima duzinu od njega (jednom) umesto da trazi maksimalnu vrednos medju duzinama svakog (racuna duzinu za ukupan broj redova). Jedino mozda nisam bas ub'o sintaksu.

[ Zoran.Eremija @ 16.04.2010. 22:46 ] @

SQL Server 2005

SELECT MAX(LEN(LTRIM(STR(Broj)))) AS Duzina, MAX(CAST('1' + LTRIM(STR(Broj)) AS int) - Broj) AS BrojIzveden
FROM dbo.Broj

SELECT dbo.Broj.Broj, RIGHT(STR(dbo.vDuzinaMax.BrojIzveden + dbo.Broj.Broj), dbo.vDuzinaMax.Duzina) AS BrojFMT
FROM dbo.vDuzinaMax CROSS JOIN
dbo.Broj

_{[Ovu poruku je menjao Zoran.Eremija dana 17.04.2010. u 09:43 GMT+1]}

[ misk0 @ 17.04.2010. 14:53 ] @

@Zidar radi, samo uz male korekcije, tabele ne mogu imati AS atribut (zasto? ne znam).

verzija koja radi:

Code (plsql):

SELECT E.Salary, LPAD(E.Salary, Q.MaxLen,'0')

FROM Employees E

INNER JOIN (SELECT MAX(LENGTH(salary)) AS MaxLen FROM employees)  Q

ON 1=1

@Shadow: kontam ustedu, izvukao si length ispred querija, ali zasto? Mislim sta se dobija sa tim? Length radi na jednoj vrijednosti, ne pita tabelu.. sta je zapravo usteda?

[ Shadowed @ 17.04.2010. 15:35 ] @

Recimo da u tabeli imas 1000 redova. U tvojoj varijanti imas 1000 racunanja length-a i trazenje najveceg od 1000 brojeva. U mojoj varijanti imas trazenje najveceg od 1000 brojeva i 1 racunanje duzine. Usteda je 999 racunanja duzine.
S' druge strane, ne bi me cudilo (mada ne znam) da baza cuva koja je max, min, count (bez uslova) i sl. vrednosti za tabelu/kolonu zasebno i da se i ne racuna prilikom svakog query-a. To bi bila izvesna optimizacija u radu dabase engine-a, ali ne znam da li se primenjuje.

[ Zidar @ 19.04.2010. 14:13 ] @

Zahvaljujem svima na resenjima :-)

Evo i mog resenja za MS SQL, koje mi sada izgleda beskonacno komplikovano u odnosu na resenja koja su dali Misko i Zoran:

Code:

IF OBJECT_ID('tempdb..#Brojevi') IS NOT NULL DROP TABLE #Brojevi
CREATE TABLE #Brojevi (Broj int NOT NULL CHECK (Broj > 0))
;
INSERT INTO #Brojevi (Broj) VALUES (9);
INSERT INTO #Brojevi (Broj) VALUES (1);
INSERT INTO #Brojevi (Broj) VALUES (100);
INSERT INTO #Brojevi (Broj) VALUES (86);
INSERT INTO #Brojevi (Broj) VALUES (10);
INSERT INTO #Brojevi (Broj) VALUES (94);
INSERT INTO #Brojevi (Broj) VALUES (195);
INSERT INTO #Brojevi (Broj) VALUES (99);
INSERT INTO #Brojevi (Broj) VALUES (6);
INSERT INTO #Brojevi (Broj) VALUES (52);
;

-- Ovo radi za slucajeve Broj > 0
WITH Zeros As
(
SELECT ZeroString = MAX(REPLICATE('0',FLOOR(LOG10(Broj))+1))
, StrLen = MAX(FLOOR(LOG10(Broj))+1)
FROM #Brojevi
)
SELECT B.Broj
, RIGHT(Z.ZeroString + CAST(B.Broj AS varchar), Z.StrLen) AS BrojFMT
FROM #Brojevi AS B
CROSS JOIN Zeros AS Z
;

Logika je ista kao kod Zorana i Miska, samo je put kojim dolazimo do stringa da se doda ispred broja malo drugaciji.

Evo i kako radi sa nulama i negativnim vrednostima. Listing je podugacak jer sam naveo i medjukorake i neuspele pokusaje, resenje je tek na dnu. Mozda bi nesto od ovog moglo da se iskombinuje u resenja koja su dali Zoran i Misko, pa da upotpunimo sliku.

Code:

IF OBJECT_ID('tempdb..#Brojevi') IS NOT NULL DROP TABLE #Brojevi
CREATE TABLE #Brojevi (Broj int NOT NULL )
;
INSERT INTO #Brojevi (Broj) VALUES (-9);
INSERT INTO #Brojevi (Broj) VALUES (1);
INSERT INTO #Brojevi (Broj) VALUES (-999);
INSERT INTO #Brojevi (Broj) VALUES (86);
INSERT INTO #Brojevi (Broj) VALUES (10);
INSERT INTO #Brojevi (Broj) VALUES (94);
INSERT INTO #Brojevi (Broj) VALUES (195);
INSERT INTO #Brojevi (Broj) VALUES  (-99);
INSERT INTO #Brojevi (Broj) VALUES (6);
INSERT INTO #Brojevi (Broj) VALUES (52);
INSERT INTO #Brojevi (Broj) VALUES (0);

;

-- Ovo ne radi, javlja gresku - LOg10(0) ne postoji, otud 'domain eror'
WITH Zeros As
(
SELECT ZeroString = MAX(REPLICATE('0',FLOOR(LOG10(Broj))+1))
        , StrLen = MAX(FLOOR(LOG10(Broj))+1)
FROM #Brojevi
)
SELECT B.Broj
, RIGHT(Z.ZeroString + CAST(B.Broj AS varchar), Z.StrLen) AS BrojFMT
FROM #Brojevi AS B
, Zeros AS Z
;
--Msg 3623, Level 16, State 1, Line 2
--A domain error occurred

-- Ovde smo eliminisali 'domain error' ali za negativne brojeve vraca nesto bez veze:
SELECT
    Broj,
    BrojFMT =
    replace(str(
                    Broj
                    , LEN  (
                            CAST
                                (
                                    (
                                    SELECT MAX(Broj)
                                    FROM #Brojevi
                                    )
                                AS varchar
                                )
                            )+1
                    )
                ,' '
                ,'0'
                )
FROM #Brojevi
;

-- This does not fail, but returns incorrect results for negatives:
SELECT
    Broj,
    BrojFMT =
    replace(str(
                    Broj
                    , LEN(CAST((SELECT MAX(Broj) FROM #Brojevi) AS varchar))+1
                    )
                ,' '
                ,'0'
                )
FROM #Brojevi
;

-- Mozemo da upotrebimo ABS da bi LOG10 izracunao duzinu i negativnih brojeva
-- i dobijamo resenje koje radi za pozitivne, nule i negativne brojeve
SELECT
Broj
, CASE
    WHEN Broj > 0 THEN '+'
    WHEN Broj = 0 THEN ' '
    WHEN Broj < 0 THEN '-'
  END
+ replace(str(
                ABS ( Broj)
                , LEN(CAST((SELECT MAX(ABS(Broj))
                            FROM #Brojevi) AS varchar))
                )
            ,' '
            ,'0'
            ) AS BrojFMT
FROM #Brojevi
ORDER BY Broj
;

[ Zidar @ 19.04.2010. 14:20 ] @

Da napomenem, mozgalice se nikad ne zatvaraju i ne zavrsavaju. Pre ili kasnije naci ce se neko ko ce ponuditi jos nek originalno resenje, poboljsanu ili drugaciju varijantu postojecih resenja, ili samo komentarisati postojeca resenja. Svaki doprinos je veoma dobrodosao. I pitanaj nizu zabranjena, jer nije uvek lako shvatiti kojim se trikom autror posluzio i zasto.

[ Zoran.Eremija @ 19.04.2010. 23:07 ] @

@Zidar, mozgalicama nikad kraja... Evo malo i '-' ...

[ djoka_l @ 20.04.2010. 11:30 ] @

Ne mogu da se uzdržim a da ne dodam i jedno Oracle specifično rešenje koje koristi analitičke funkcije

Dakle evo rešenja:

Code (sql):

SELECT amount,

       to_char(amount,

                lpad('0', LENGTH(MAX(amount) OVER()), '0')) formated

  FROM sales_actuals

Funkcija

Code:
max(amount) over()

računa maksimum od amount nad celim aktivnim setom i to SAMO JEDNOM za ovaj upit. Kao ilustracija, evo execution plana ovog upita

Za razliku od prvog rešenja, sledeće rešenje

Code (sql):

SELECT amount,

       to_char(amount,

                lpad('0', LENGTH(x.m), '0')) formated

  FROM sales_actuals, (SELECT MAX(amount) m FROM sales_actuals) x

za svaki red u spoljnom upitu ponovo traži max u unutrašnjem.

EDIT: rešenja koja su dali misk0 i Shadowed, takođe koriste ovaj drugi execution plan koji je suboptimalan...

_{[Ovu poruku je menjao djoka_l dana 20.04.2010. u 13:56 GMT+1]}

_{[Ovu poruku je menjao djoka_l dana 20.04.2010. u 13:56 GMT+1]}

[ misk0 @ 20.04.2010. 14:24 ] @

Citat:

djoka_l:
EDIT: rešenja koja su dali misk0 i Shadowed, takođe koriste ovaj drugi execution plan koji je suboptimalan...

Ne trebas se suzdrzavat, samo udri :)

Mozes napisati kako i gdje dobijas taj izvjestaj prikazan na screenshot-ovaim?
Vezano za izvrsavanje, citao sam da su subquery sa single i multiple rows rezultatima koji ne uzimaju vrijednosti iz spoljasnjeg querija izvrsavaju samo jednom za citavu tabelu a ne i za svaku iteraciju, dok 'correlated' queriji koji zavise od svake iteracije se izvrsavaju koliko ima i iteracija.??

edit:
nashao sam gdje mogu to da vidim - SQL Developer ->Explain i dobio screen ali ja to ne znam procitati a ono sto me interesuje je koliko puta se izvrsava subquery koji racuna maximalnu sirinu broja?

[ djoka_l @ 20.04.2010. 16:43 ] @

Nisam bio u pravu, ovaj tvoj upit koji si analizirao, kao i onaj koji je sa analitičkom funkcijom daju isti execution plan (EDIT: ne daju isti plan, ali isto "košta"). Očigledno sam sa cut&paste preneo i analizirao pogrešan upit.

Plan kaže da će prvo da pročita celu tabelu i sortira je (kako bi našao najveći element), a onda još jednom da je pročita kako bi dao rezultat.
Ovde je dobro što se ne pojavljuje "NESTED LOOPS" koji se dešava kod kverija unutar kverija...

Inače, ja koristim PL/SQL developer, ali svaki developer alat ima mogućnosti da prikaže plan, a čak i u običnom SQL*Plusu to može da se napravi, ali pešački...

_{[Ovu poruku je menjao djoka_l dana 20.04.2010. u 17:54 GMT+1]}

[ Zidar @ 20.04.2010. 17:10 ] @

zahvaljujem Zoranu na resenju za negativne brojeve u Accessu.

Evo ijedno prosto resenje u Accesu, radi nule, pozitivne i negativne brojeve. Koristi Zoranov query QDuzinaMAx

QDuzinaMAx

Code:

SELECT MAX(Len(Trim(Str([Broj]))))                AS duzina,
       MAX(Val("1" & Trim(Str([Broj]))) - [Broj]) AS brojizveden
FROM   broj;

Kveri koji formatira dinamicki dodaje nule ispred brojeva, i znak minus gde treba:

Code:

SELECT Broj.Broj,
       Format([broj], String([QDuzinaMax].[Duzina], "0")) AS BrojFmt
FROM   Broj,
       QDuzinaMax;

Svi smo zaboravili na Accessove ugradjene funkcije (nisu po standardu SQL, ali vrede). Funkcija format formatira broj saglasno svom drugom argumentu. Za dinamicko gradjenje drugog argumenta u funkciji FORMAT upotrebili smo funkciju STRING ( duzina, karakter) koja zadati 'karakter' ponavjla 'duzina' puta. U nasem slucaju 'duzina' dolazi iz kverija qduzinamax

Kako bi rekao Trle sa foruma Access - ima mali milion funkcija u Accesu koje nam olaksavaju zivot, pa ne moramo da programiramo'