top x unutr neke grupe

top x unutr neke grupe 11.07.2011. u 22:18 · pre 186 meseci #1

Pozdrav svima,
potrebna mi je pomoc oko upita koji bi trebao dati prikaz top 5 unutar neke grupe. Da bi bolje objasnio problem navodim primer:

Tabela Radnik:

id_radnik; radnik; odeljenje; bodovi
1; Marko;1;18
2;Sinisa;1;44
3;Jelena;2;22
4;Tatjana;2;37
5;Stojan;4;76
6;Nenad;3;55
7;Jadranka;3;137
8;Natasa;2;23
9;Bojan;2;88
10;Mirko;1;17
11;Andjelko;5;11
12;Dragana;3;56
13;Pavle;2;41
14;Tamara;3;99
15;Marko;2;105
16;Oliver;1;134
17;Vladimir;3;40
...

Potreban mi je select koji bi prikazao po 5 radnika sa najvecim brojem bodova za svako odeljenje. U gore navedenim podacima se vidi da postoji recimo 4 odeljenja. Kako da prikazem po 5 ("najboljih") radnika svakog tog odeljenja?

Re: top x unutr neke grupe 12.07.2011. u 07:17 · pre 186 meseci #2

Verovatno postoje bolja rešenja, ali evo jednog na brzinu

Code:

DECLARE @T TABLE (id_radnik INT, radnik NVARCHAR(30), odeljenje INT, bodovi INT, RBr INT IDENTITY(1,1), RBrOdeljenja INT)
INSERT INTO @T (id_radnik, radnik, odeljenje, bodovi, RBrOdeljenja)
SELECT id_radnik, radnik, odeljenje, bodovi, 1 FROM Radnik ORDER BY odeljenje, bodovi DESC

UPDATE @T SET RBrOdeljenja = t.RBr - (SELECT MIN(t1.RBr) - 1 FROM @T t1 WHERE t1.odeljenje = t.odeljenje) FROM @T t

SELECT * FROM @T WHERE RBrOdeljenja < 6 ORDER BY odeljenje, bodovi DESC

Pozdrav

Re: top x unutr neke grupe 12.07.2011. u 08:28 · pre 186 meseci #3

Code:

declare @tmp table (id int, ime nvarchar(50), odeljenje int, bodovi int)

insert into @tmp select 1, 'Marko', 1, 18
insert into @tmp select 2, 'Sinisa', 1, 44
insert into @tmp select 3, 'Jelena', 2, 22
insert into @tmp select 4, 'Tatjana', 2, 37
insert into @tmp select 5, 'Stojan', 4, 76
insert into @tmp select 6, 'Nenad', 3, 55
insert into @tmp select 7, 'Jadranka', 3, 137
insert into @tmp select 8, 'Natasa', 2, 23
insert into @tmp select 9, 'Bojan', 2, 88
insert into @tmp select 10, 'Mirko', 1, 17
insert into @tmp select 11, 'Andjelko', 5, 11
insert into @tmp select 12, 'Dragana', 3, 56
insert into @tmp select 13, 'Pavle', 2, 41
insert into @tmp select 14, 'Tamara', 3, 99
insert into @tmp select 15, 'Marko', 2, 105
insert into @tmp select 16, 'Oliver', 1, 134
insert into @tmp select 17, 'Vladimir', 3, 40

;with e as (
select id
    ,ime
    ,odeljenje
    ,bodovi
    ,row_number() over (partition by odeljenje order by bodovi desc) as rb
    from @tmp
)
select * from e
where rb <=5

Potpis

Naučio sam...
Da je važnije biti ljubazan nego biti u pravu

2

Re: top x unutr neke grupe 12.07.2011. u 14:36 · pre 186 meseci #4

Obojci se zahvaljujem na odgovoru.

vujkev, tvoje rešenje je stvarno elegantno i kad ga vidim prosto me je sramota šta sam sve pokušavao.

Re: top x unutr neke grupe 23.07.2011. u 14:11 · pre 185 meseci #5

Da ne otvaram novu temu. Ovaj primer me donekle podseca na moj problem. Razlika je je recimo u tome što bi trebalo na ovu tabelu dodati i kolonu datum pa onda na dnevnom nivou prikazivati najboljih 5 ali samo unutar svog odljenja. Kako doći do top liste po danima?

Hvala

Re: top x unutr neke grupe 25.07.2011. u 15:20 · pre 185 meseci #6

@ akioki:

Citat:

Razlika je je recimo u tome što bi trebalo na ovu tabelu dodati i kolonu datum pa onda na dnevnom nivou prikazivati najboljih 5 ali samo unutar svog odljenja.

Bice mnogo lakse da ti neko napise odgovor ako zakacis skriptu sa definicijom tabele i nesto podataka. Niko nema vremena napretek. Ono sto je Vujkev uradio, sam napisao scriptu za kreiranje tabele i test podatke, to je izuzetak a ne pravilo.

Re: top x unutr neke grupe 25.07.2011. u 23:44 · pre 185 meseci #7

Zidar, potpno si u pravu.

Evo primera mog slucaja:

Code (sql):

GO

/****** Object:  Table [dbo].[prodavnica]    Script Date: 07/25/2011 23:29:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[prodavnica](

     [prodavnica_id] [INT] IDENTITY(1,1) NOT NULL,

     [prodavnica] [nvarchar](50) NULL,

 CONSTRAINT [PK_prodavnica] PRIMARY KEY CLUSTERED 

(

     [prodavnica_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[prodavnica] ON

INSERT [dbo].[prodavnica] ([prodavnica_id], [prodavnica]) VALUES (1, N'A Bike Store')

INSERT [dbo].[prodavnica] ([prodavnica_id], [prodavnica]) VALUES (2, N'Progressive Sports')

INSERT [dbo].[prodavnica] ([prodavnica_id], [prodavnica]) VALUES (3, N'Advanced Bike Components')

SET IDENTITY_INSERT [dbo].[prodavnica] OFF

/****** Object:  Table [dbo].[musterija]    Script Date: 07/25/2011 23:29:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[musterija](

     [musterija_ID] [INT] IDENTITY(1,1) NOT NULL,

     [Ime] [nvarchar](50) NULL,

     [Prezime] [nvarchar](50) NULL,

     [SerijskiBroj] [NCHAR](10) NULL,

 CONSTRAINT [PK_musterija] PRIMARY KEY CLUSTERED 

(

     [musterija_ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[musterija] ON

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (1, N'Gustavo', N'Achong', N'398       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (2, N'Catherine', N'Abel', N'747       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (3, N'Kim', N'Abercrombie', N'334       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (4, N'Humberto', N'Acevedo', N'599       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (5, N'Pilar', N'Ackerman', N'554       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (6, N'Frances', N'Adams', N'991       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (7, N'Margaret', N'Smith', N'959       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (8, N'Carla', N'Adams', N'107       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (9, N'Jay', N'Adams', N'158       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (10, N'Ronald', N'Adina', N'453       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (11, N'Samuel', N'Agcaoili', N'554       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (12, N'James', N'Aguilar', N'333       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (13, N'Robert', N'Ahlering', N'678       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (14, N'François', N'Ferrier', N'571       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (15, N'Kim', N'Akers', N'440       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (16, N'Lili', N'Alameda', N'123       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (17, N'Amy', N'Alberts', N'727       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (18, N'Anna', N'Albright', N'197       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (19, N'Milton', N'Albury', N'492       ')

INSERT [dbo].[musterija] ([musterija_ID], [Ime], [Prezime], [SerijskiBroj]) VALUES (20, N'Paul', N'Alcorn', N'331       ')

SET IDENTITY_INSERT [dbo].[musterija] OFF

/****** Object:  Table [dbo].[racun]    Script Date: 07/25/2011 23:29:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[racun](

     [racun_ID] [INT] IDENTITY(1,1) NOT NULL,

     [musterija_ID] [INT] NULL,

     [prodavnica_ID] [INT] NULL,

     [vreme] [datetime] NULL,

     [iznos] [INT] NULL,

 CONSTRAINT [PK_racun] PRIMARY KEY CLUSTERED 

(

     [racun_ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[racun] ON

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (1, 1, 1, CAST(0x00009F1D016A9309 AS DateTime), 234)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (2, 1, 2, CAST(0x00009F1D016A9934 AS DateTime), 3242)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (3, 2, 3, CAST(0x00009F1D016ACB39 AS DateTime), 876)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (4, 6, 3, CAST(0x00009F1A016ACDCC AS DateTime), 567)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (5, 5, 3, CAST(0x00009F1A016AD372 AS DateTime), 8999)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (6, 4, 2, CAST(0x00009F1A016AD77F AS DateTime), 654)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (7, 6, 2, CAST(0x00009F1A016ADCA7 AS DateTime), 4777)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (8, 1, 1, CAST(0x00009F1D016AE0E7 AS DateTime), 4332)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (9, 2, 1, CAST(0x00009F21016AE568 AS DateTime), 3567)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (10, 2, 1, CAST(0x00009F26016AEB4F AS DateTime), 78900)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (11, 12, 1, CAST(0x00009F1D016AF0CF AS DateTime), 65444)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (12, 17, 3, CAST(0x00009F1D016AF7A7 AS DateTime), 67)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (13, 10, 3, CAST(0x00009F1D016B0118 AS DateTime), 7789)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (14, 4, 3, CAST(0x00009F1A016B03F8 AS DateTime), 7589)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (15, 5, 3, CAST(0x00009F1A016B0708 AS DateTime), 98)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (16, 7, 2, CAST(0x00009EFC016B0BBB AS DateTime), 8777)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (17, 1, 2, CAST(0x00009F1A016B1249 AS DateTime), 664)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (18, 4, 2, CAST(0x00009F1A016B18FD AS DateTime), 4545)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (19, 6, 2, CAST(0x00009F1A016B204F AS DateTime), 333)

INSERT [dbo].[racun] ([racun_ID], [musterija_ID], [prodavnica_ID], [vreme], [iznos]) VALUES (20, 6, 2, CAST(0x00009F1D016B2E35 AS DateTime), 456)

SET IDENTITY_INSERT [dbo].[racun] OFF

/****** Object:  Default [DF_racun_vreme]    Script Date: 07/25/2011 23:29:02 ******/

ALTER TABLE [dbo].[racun] ADD  CONSTRAINT [DF_racun_vreme]  DEFAULT (getdate()) FOR [vreme]

GO

/****** Object:  ForeignKey [FK_racun_musterija]    Script Date: 07/25/2011 23:29:02 ******/

ALTER TABLE [dbo].[racun]  WITH CHECK ADD  CONSTRAINT [FK_racun_musterija] FOREIGN KEY([musterija_ID])

REFERENCES [dbo].[musterija] ([musterija_ID])

GO

ALTER TABLE [dbo].[racun] CHECK CONSTRAINT [FK_racun_musterija]

GO

/****** Object:  ForeignKey [FK_racun_prodavnica]    Script Date: 07/25/2011 23:29:02 ******/

ALTER TABLE [dbo].[racun]  WITH CHECK ADD  CONSTRAINT [FK_racun_prodavnica] FOREIGN KEY([prodavnica_ID])

REFERENCES [dbo].[prodavnica] ([prodavnica_id])

GO

ALTER TABLE [dbo].[racun] CHECK CONSTRAINT [FK_racun_prodavnica]

GO

Ono što mi treba jeste da dobijem dnevnu listu top 5 računa za svaku prodavnicu posebno ali tako da se u tih 5 računa ne mogu pojaiti dva računa od iste mušterije. Ukoliko u toku jednog dana musterija ima više računa, računa se samo onaj sa najvećim iznosom. Mušterija se može pojaviti u više prodavnica u toku jednog dana.

Definitivno sam se spetljao oko ovog problema i neumem doći do potpunog rešenja.

Svaka pomoć je više nego dobrodošla.

Re: top x unutr neke grupe 26.07.2011. u 08:45 · pre 185 meseci #8

ovo bi trebalo da je ispravno

Code:
; with e as (
select racun_id
    ,musterija_id
    ,prodavnica_id
    ,vreme
    ,iznos
    ,row_number() over (partition by musterija_id, prodavnica_id order by iznos desc) as rb_racuna
     from racun
     )
     select *
     from e
     where rb_racuna = 1

rb_racuna je broj racuna musterije u prodavnici sortiran po iznosu u opadajućem redosledu. Tebi trebaju samo prvi računi

Potpis

Naučio sam...
Da je važnije biti ljubazan nego biti u pravu

Re: top x unutr neke grupe 26.07.2011. u 23:09 · pre 185 meseci #9

Zurim kuci, sutra cemo da diskutujemo korak po korak. Evo konacno resenje, pokazuje po dva najveca racuna po prodavnici po danu. Svaka musterija se moze medju najvecim racunima pojaviti samo jednom po prodavnici i danu. Ako musterija ima vise racuna u jednom danu u jednoj prodavnici, uzima se samo najveci od njih.

Code:

WITH RacuniPoDatumu AS
(-- prvo svodimo vreme na dane, bez minuta i sekundi, lkase je kasnije
SELECT
Racun_ID, Musterija_id, prodavnica_id, iznos, vreme
, Datum = dateadd( dd,  datediff (dd,0,vreme),0)
FROM racun
)
, RacuniPoMusteriji AS
(-- ovde odredjuemo redni broj za racune musterija,
--- vazno ako ima bvise od jednog po prodavnic po danu za isut musteriju
SELECT Datum, Prodavnica_id, Musterija_id,  Racun_ID, iznos
, Redosled = row_number() OVER (PARTITION BY Datum, Prodavnica_id, Musterija_id
                                ORDER BY Iznos DESC)
FROM RacuniPoDatumu
)
, PoluFinale AS
(-- sad uzmemo za svaki (Dan,prodavnicu) samo njaveci racun za svaku musteriju
SELECT Datum, Prodavnica_id, Musterija_id,  Racun_ID, iznos , Redosled
-- Konacni redosled =redoseld po velicni iznoa, opadajuci u okviru (Dan,prodavnica),
-- gde smo za svaku musteriju uzeli
-- u obzir njen najveci racun u toj radnji tog dana
, KonacniRedosled = row_number() OVER (Partition BY Datum, Prodavnica_id ORDER BY Iznos)
FROM RacuniPoMusteriji
WHERE Redosled  = 1
)
--
SELECT
Datum, Prodavnica_id, Musterija_id,  Racun_ID, iznos , Redosled
, KOnacniRedosled
FROM PoluFinale
WHERE KOnacniRedosled <=2   -- ovde moze <=5 za TOP 5 racuna
ORDER BY Datum, Prodavnica_id

Re: top x unutr neke grupe 27.07.2011. u 15:05 · pre 185 meseci #10

Sve jasno, nema pitanja? Cool

Re: top x unutr neke grupe 27.07.2011. u 22:06 · pre 185 meseci #11

Ćao ljudi,
hvala vam na pomoći. Izvinite što nisam stigao ranije odgovoriti i zahvaliti se (bio sam odsutan).

Vujkev, na žalost tvoje rešenje je identično mome zbog kojeg sam i zatražio pomoć na forumu pošto ne daje dobar prikaz. Moram reći da mi je tvoj select koji si dao windows useru dao inspiraciju ali nisam uspeo doterati to kako treba.

Zidar, svaka čast! Tvoje rešenje je ono što mi treba. Uz sve ove tvoje komentare stvarno nema šta da bude nejasno. Ako bi cepidlačio jedini sto treba dodati jeste ORDER BY iznos DESC u PoluFinalu.

Očigledo je da mi treba još puno puno vežbi sa SQL-om. Resenje se svelo na nekoliko "jednostavnih" select-a (koje navodno znam) ali nisam uspeo biti dovoljno kreativan da ih primenim. Nadam se da to dolazi vremenom.

Stvarno cenim vaše angažovanje na forumu. Veliko vam hvala.

Re: top x unutr neke grupe 28.07.2011. u 17:55 · pre 185 meseci #12

nema na cemu, vazno je da si shvatio sta se desava

Citat:

Ako bi cepidlačio jedini sto treba dodati jeste ORDER BY iznos DESC u PoluFinalu.

Odlicno zapazanje, dokaz da si razumeo sta se desava.

Samo radi, ukoliko je moguce na stvarnim a ne zamisljenim projektima, sa zivim korisnicaima. Tako se najbolje nauci.

Srecno

Forum kao aplikacija na telefonu

Srodne teme