Jump to content

Kā uzrakstīt šādu pieprasījumu?


pX79
 Share

Recommended Posts

Sveiki.

 

Ir aizķeršanās ar vienu SQL pieprasījumu. Kā platforma tiek lietots MS SQL 2005.

 

Piemēram tabula.

 

ID	DATE		ADDRESS_ID
1	2008.01.01	5
2	2007.01.01	5
3	2008.01.01	6
4	2008.01.01	7

 

No šī ir vajadzīgs dabūt ārā unikālus address_id ierakstus ar lielāko datumu un atiecīgo ID.

Kā to var uzrakstīt SQL?

 

Ja raksta piemēram

 

SELECT MAX(ID), MAX(DATE), ADDRESS_ID

FROM table

GROUP BY ADDRESS_ID

 

tad izdod protams nepareizu ierakstu pie address_id=5

 

2, 2008.01.01, 5

 

bet vajag

 

1, 2008.01.01, 5

Link to comment
Share on other sites

Neieslīgstot lielās gudrībās

SELECT

ID, DATE, ADDRESS_ID

FROM table T

INNER JOIN

(SELECT DATE, MAX(ID) AS ID

FROM table

GROUP BY DATE) as SUBQ on SUBQ.DATE = T.DATE and SUBQ.ID = T.ID

Link to comment
Share on other sites

Tad izdod tikai divus ierakstus no vajadzīgajiem trim.

 

4	01.01.2008 0:00:00	7
2	01.01.2007 0:00:00	5

 

Vajadzīgs lai atgriež šādu rezultātu

1	01.01.2008 0:00:00	5
3	01.01.2008 0:00:00	6
4	01.01.2008 0:00:00	7

Edited by pX79
Link to comment
Share on other sites

Yap, subselekti. Tikai barakudai tur kaut kas jokains uzraxtīts. Nesaku, ka nepareizi, bet šobrīd nespēju saprast cēlo domu. :p Es rakstītu šādi:

select a.id, a.date, a.address_id
from [table] a inner join (select b.address_id, max(b.date) md from [table] b group by b.address_id) c on (a.address_id = c.address_id and a.date = c.md)

Vienīgi šis kverijs atgriezīs vairākas rindas uz vienu address_id, ja būs vairākas rindas ar maximālo datumu. Ja vajag arī no tā tikt vaļā, tad var rakstīt kaut kā šādi:

select max(a.id) id,  a.date, a.address_id
from [table] a inner join (select b.address_id, max(b.date) md from [table] b group by b.address_id) c on (a.address_id = c.address_id and a.date = c.md) group by a.address_id, a.date

 

Pievienots: Tikai baidos, ka performance gan nebūs neko spīdoša. Varbūt to varētu uzlabot ar kaut kādiem indexiem, bet arī tad būs grūti. Diemžēl nezinu labāku risinājumu. :(

Link to comment
Share on other sites

Gints Plivna

Var izmantot analītiskās f-jas, tas, protams, nenozīmē, ka šis vienmēr būs labākais risinājums ;)

 

with a as (
 select 1 id, cast('2008.01.01' as datetime) date, 5 address_id
 UNION ALL
 select 2, '2007.01.01', 5
 UNION ALL
 select 3, '2008.01.01', 6
 UNION ALL
 select 4, '2008.01.01', 7
)
select address_id, date, id from (
 select address_id, date, id, row_number() over (partition by address_id order by date desc) rn
 from a
) q
where rn = 1 

5	2008-01-01 00:00:00.000	1
6	2008-01-01 00:00:00.000	3
7	2008-01-01 00:00:00.000	4

 

P.S. Kā redzams with klauza ir tīri tāpēc lai ģenerētu testpiemēru, nekam citam.

 

Gints Plivna

http://datubazes.wordpress.com

Link to comment
Share on other sites

Yap, subselekti. Tikai barakudai tur kaut kas jokains uzraxtīts. Nesaku, ka nepareizi, bet šobrīd nespēju saprast cēlo domu.

 

Ta ir labs - tā vajadzība bija tik jokaini uzrakstīta, ka uzreizi nevarēju iebraukt. Tā uz vienu aci padomāju, ka vajadzīgs lielākais id katrā datumā + attiecīgais address id.

Link to comment
Share on other sites

Mazliet offtopic - kāds var apstāstīt šitos over...partition brīnumus? Cik esmu dokumentācijās lasījis, nekādi nevarēju iebraukt. :p Lai arī saprotu, ka tas ir spēcīgs rīks tiešī šādiem gadījumiem. :p

Link to comment
Share on other sites

Gints Plivna

Tātad saskaņā ar vienu džeku vārdā Thomas Kyte (Oracleistiem vajadzētu zināt :) analītiskās f-jas ir labākais kas izgudrots kopš select teikuma :)

 

Ideja tāda, ka normāli SQLā ir iespējams skatīties tikai uz izvēlētās rindiņas kolonām - tātad no izvēlētās rindiņas var paņemt kolonas a un b un piemēram saskaitīt. Taču tiklīdz kā grib dabūt kādas citas rindiņas (saskaņā ar definētu sakārtojumu, protams) kolonas tā ir nepieciešams savienojums (join). Nu tad lūk analītiskās f-jas ļauj paskatīties uz iepriekšējo, nākošo, n pirms šīs, n pēc šīs, pirmo kādā grupā,pēdējo kādā grupā utt utjp rindiņu kolonām bez savienojuma.

ar partition by - atlasītās kopas datus sadala nosacītās grupās

ar order by sakārto

 

tātad ir f-ja row_number(), kas vienkārši dod numurus pēc kārtas, 1, 2, 3, ..., n

tātad piemēram row_number() over (partition by a order by b) nozīmē, to ka pēc kolonas a vērtībām rezultāta dati tiks sadalīti kopās. Katrā kopā numurēšana tiks sākta no sākuma, un numuri tiks dalīti sakārtojot pēc kolonas b

tātad ja mums ir dati:

a b
1 2
1 3
2 5
2 1
3 0

tad row_number() over (partition by a order by b) būs šāds:

SQL> with a as (
 2	select 1 a, 2 b from dual
 3	union all
 4	select 1, 3 from dual
 5	union all
 6	select 2, 5 from dual
 7	union all
 8	select 2, 1 from dual
 9	union all
10	select 3, 0 from dual
11  )
12  select a, b, row_number() over (partition by a order by b) rn
13  from a
14  /

			   A					B				   RN
-------------------- -------------------- --------------------
			   1					2					1
			   1					3					2
			   2					1					1
			   2					5					2
			   3					0					1

 

savukārt ja noraujam partition by nost, tad visa kopa ir kā viens un numurē kā nu sanāk:

SQL> with a as (
 2	select 1 a, 2 b from dual
 3	union all
 4	select 1, 3 from dual
 5	union all
 6	select 2, 5 from dual
 7	union all
 8	select 2, 1 from dual
 9	union all
10	select 3, 0 from dual
11  )
12  select a, b, row_number() over (order by b) rn
13  from a
14  /

			   A					B				   RN
-------------------- -------------------- --------------------
			   3					0					1
			   2					1					2
			   1					2					3
			   1					3					4
			   2					5					5

 

kādas ir pieejamās f-jas to jāskatās katrā DB un versijā atsevišķi, piemēram f-ja lag, kas skatās atpakaļ:

SQL> with a as (
 2	select 1 a, 2 b from dual
 3	union all
 4	select 1, 3 from dual
 5	union all
 6	select 2, 5 from dual
 7	union all
 8	select 2, 1 from dual
 9	union all
10	select 3, 0 from dual
11  )
12  select a, b, lag(b) over (order by b) iepriekshejais_b
13  from a
14  /

			   A					B	 IEPRIEKSHEJAIS_B
-------------------- -------------------- --------------------
			   3					0
			   2					1					0
			   1					2					1
			   1					3					2
			   2					5					3

 

Tā kā izskatās, ka tauta Latvijā ne pārāk par tām ir informēta lai gan Oraclē kaut kas jau bija sākot ar 8.1.7.x versijām, tad kaut kādā brīdi uzrakstīšu par tām savā blogā - stay tuned latviski izsakoties! :)

 

Gints Plivna

http://datubazes.wordpress.com

Edited by Gints Plivna
Link to comment
Share on other sites

To, ka viņas tur ir jau diezgan sen, es jau zinu. Pagaidām tikai neesmu izpētījis smalki. Jāsaka, vienu reizi izlasot šo paskaidrojumu man arī nepieleca. Būs vēlāk vēl jāpadomā par tēmu. :p Iemet PZ, kad uzrakstīsi paskaidrojumu savā blogā, ok? :)

 

Pievienots: Ā, laikam tā kā pieleca. Interesanti. Kad nākamoreiz saskaršos ar līdzīgu problēmu, paturēšu prātā šādu iespēju. :)

Link to comment
Share on other sites

Gints Plivna

Ja atcerēšos, iemetīšu :)

 

pievērs uzmanību partition by a -

row_number() pie katras jaunas a vērtības sāk skaitīšanu no sākuma un skaita kārtībā order by b

 

ja partition by a izrauj ārā, tad skaitīšanu sāk sākumā no viens, bet nekur no sākuma atsākt nevajag, jo visa kopa ir viena liela grupa.

 

lag savukārt vienkārši ļauj redzēt iepriekšējo b vērtību definētajam sakārtojumam. Protams pirmajam tā nav definēta, tāpēc null. Arī tajā protams var iebāzt iekšā to partition by, bet nu iesākumam ir labi tāpat :)

 

Gints Plivna

http://datubazes.wordpress.com

 

Pievienots:

 

OK visiem, kam ir interese - ievads SQL analītiskajās funkcijās.

Ņemiet par labu ;)

 

Gints

Edited by Gints Plivna
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...