[MSSQL] Twee losse queries in 1 combineren met/zonder join?

[Verwijderd]

Hey mensen,

Ik heb een tabel met 3 kolommen (we houden het effe simpel)

itemID => primary key
Date_Created => Datum dat het item is aangemaakt
Date_Modified => Datum dat het item voor het laatst is aangepast

Ik wil deze data vertalen naar een tabel waarin ik per week kan zien hoeveel items er in die week zijn aangemaakt en aangepast. Dit verteld me namelijk iets over de inname van nieuwe items, maar ook over hoe noodzakelijk het is om items weer eens te gaan herzien

Ik ben momenteel zo ver dat ik weet hoe ik beide los voor elkaar moet krijgen:

Voor de Created:

SQL:

SELECT 
    Datename(YEAR, Date_Created) as YEAR, 
    Datename(WEEK, Date_Created) as WEEK, 
    COUNT (*) as COUNT_CREATED
FROM 
   SomeTable
GROUP BY
    Datename(YEAR, Date_Created), 
    Datename(WEEK, Date_Created)

Appeltje-eitje... De volgende is wat lastiger maar nog steeds te volgen:

SQL:

SELECT 
    Datename(YEAR, Date_Modified) as YEAR, 
    Datename(WEEK, Date_Modified) as WEEK, 
    COUNT (
          CASE WHEN 
                 Date_Modified < DATEADD (hh, 12, Date_Created) 
          THEN 
                 NULL
          ELSE
                 Date_Modified
          END) AS COUNT_MODIFIED
FROM 
   SomeTable
GROUP BY
    Datename(YEAR, Date_Modified), 
    Datename(WEEK, Date_Modified)

Die CASE WHEN staat er in omdat ik een modify op de eerste dag nog niet echt een modify vind - je bent dan nog steeds met dat item bezig geweest. Die CASE WHEN mag van mij weg, maar dit leek me gewoon wat netter.

Maar toen kwam de echte uitdaging: Hoe voeg ik deze twee nu samen? Ik zoek dus naar een query die beide bovenstaande query's combineert en via een join op de jaar en week getallen de resultaten met elkaar combineert.

Een bijkomend probleem is dat ik gaten moet toe laten staan: in sommige weken zullen er geen nieuwe items zijn aangemaakt, of geen items zijn aangepast. Pas als er in een week geen nieuwe items zijn aangemaakt, noch zijn aangepast, mag van mij de week komen te vervallen. Een JOIN gebruiken kan dan volgens mij niet.

Hebben jullie suggesties om dit probleem op te lossen. Ik kan het wel oplossen met views, maar ik wil ook graag weten hoe ik dit voor elkaar krijg zonder views (al is het maar om het leereffect)

[Niemand_Anders]

Waarom schrijf je niet gewoon de resultaten van beide queries weg naar een tijdelijke tabel. Vervolgens kun je met een simpele query de resultaten samen vatten:

SQL:

 select year, month, count(id) from #report group by year, month select by year desc, month desc

(laatste periode wordt als eerste getoond, omdat men meestal alleen is geinteresseerd in de verandering ten opzicht van de vorige rapportage).

Als je bij het vullen van de tijdelijke tabel ook nog de source bijhoud, dan zou je zelfs kunnen querien (tellen) op de bron

SQL:

select year, month, count(id), sum(case sourceid when 1 then 1 else 0 end) as created, sum(case sourceid when 2 then 1 else 0 end) as modified from #report

[Verwijderd]

hey Niemand_Anders,

Ik snap de query die je maakt niet helemaal.

SQL:

SELECT 
          year, 
          month, 
          count(id) 
FROM 
         #report 
GROUP BY 
         year, 
         month 
SELECT BY 
         year desc, 
         month desc

De SELECT BY is nieuw voor me, ik gebruik daar ORDER BY voor, ik vermoed dat dat het enige verschil is. Verder zie ik hier alleen maar het ophalen van de tabel die ik in 1 van de queries heb gemaakt. Ik kan het natuurlijk mishebben, maar waar is de link naar de tweede tabel?

Over de tweede query heb ik ook een vraag:

SQL:

SELECT 
            year, 
            month, 
            count(id), 
            sum(case sourceid when 1 then 1 else 0 end) as created, 
            sum(case sourceid when 2 then 1 else 0 end) as modified 
FROM
            #report

Wat betekent sourceid hier en waar is (ook) hier de link naar de tweede table?

Het maken van tijdelijke tables in de database klinkt me wat eng: ik wil zo min mogelijk schrijven om twee redenen:

- Het is een database van een programma wat ik zelf niet heb gebouwd, ik weet dus niet precies wat er onder water allemaal zit.
- Het moet ook door leken gebruikt kunnen worden: ik heb daarom liever dat de query wat ingewikkelder wordt maar hoogstens faalt, dan dat ze ineens gaan lopen hacken in een query en een verkeerde table droppen.

Misschien is dit overdreven voorzichtigheid, dus mogen jullie voorstellen hebben om dit risico te beperken, dan sta ik daar zeker open voor.

Even los van hoe ik de twee resultaten combineer zit ik nu alsnog met een vraag: hoe join ik de tables op zo'n manier dat ik geen gegevens verlies: zoals ik al zei: een join geeft alleen een resultaat terug als beide tabellen een row hebben die aan de ON voorwaarden voldoen, maar het kan dus zijn dat dit niet zo is.

Alvast bedankt voor de reactie!

[Janoz]

Pas als er in een week geen nieuwe items zijn aangemaakt, noch zijn aangepast, mag van mij de week komen te vervallen.

Als dat mag en niet hoeft zou je ook tegen een hulp tabel aan kunnen joinen waarin je alle jaartallen en weeknummers hebt staan.

Even los van hoe ik de twee resultaten combineer zit ik nu alsnog met een vraag: hoe join ik de tables op zo'n manier dat ik geen gegevens verlies: zoals ik al zei: een join geeft alleen een resultaat terug als beide tabellen een row hebben die aan de ON voorwaarden voldoen, maar het kan dus zijn dat dit niet zo is.

Om alles terug te krijgen moet je een FULL OUTER JOIN gebruiken.

[ Voor 43% gewijzigd door Janoz op 07-01-2008 15:21 ]

[Moraelyn]

Met een full outer join kun je de tabellen aan elkaar koppelen (d.m.v. een alias SomeTable s1, SomeTable s2) en de huidige code blijven gebruiken. Je moet de count(*) op de ene alias doen en de case op de andere.

Een tijdelijke tabel is een tabel die alleen in het geheugen bestaat bij MSSQL. Zelfs dat is in dit geval nog niet nodig aangezien je beide queries ook als subselect kunt opnemen.

code:

SELECT 
          year, 
          month, 
          (select count(itemID) from SomeTable where year = s1.year and month = s1.month)
          (select count(itemID) from SomeTable where year = s1.year and month = s1.month and Date_Modified < DATEADD (hh, 12, Date_Created))
FROM 
         SomeTable s1
GROUP BY 
         year, 
         month

[KabouterSuper]

UNION.... waarom moeilijk doen als het makkelijk kan?

[Niemand_Anders]

select by is inderdaad order by.

De beide queries welke je nu gebruikt geef je een extra kolom 'sourceid'

SQL:

SELECT 
    Datename(YEAR, Date_Created) as YEAR, 
    Datename(WEEK, Date_Created) as WEEK, 
    COUNT (*) as COUNT_CREATED,
    1 as sourceid
FROM 
   SomeTable .........

Bij de tweede query voeg je een '2 as sourceid' toe. Eventueel zou je zelfs een telling op het aantal verwijderde records op deze manier kunnen realiseren (sourceid = 3)

Tijdelijke tabellen worden verwijderd zodra de connectie wordt beeindigd op de database server. De tijdelijke tabel bestaat dus alleen voor jouw script/connectie(id). Zie ook books online onder 'create (temp) table'

Omdat mijn query de tijdelijke tabel in combinatie met de group by gebruikt hoef je niet meer te joinen. Daarnaast blijven de 'source' queries relatief eenvoudig. Je hoeft alleen maar voor de velden year, week, count (en sourceid) te zorgen en je kunt alle resultaten eenvoudig optellen via de count functie. Als query 1 als resultaat '0' geeft en query 2 als resultaat '5', dus zal je dus gewoon '5' als volledig resultaat terug krijgen. Je krijgt alleen geen resultaten terug als beide queries geen resultaten hebben opgelevert voor de combinatie year/week.

Als je het script als stored procedure opneemt in de database, dan hoeft je ook niet bang te zijn dat andere (wannabe) developers rare database veranderingen doorvoeren.

Los van welke methode je kies, zul je altijd een methode moeten vinden om je queries werkend te houden. Want op het moment dat andere de database aanpassen (refactoring van applicatie), dan is de mogelijkheid aanwezig dat jouw queries niet meer werken. Je zult dus zelf een regression test moeten maken waarbij je beide queries test. Als je resultaten terug krijgt, dan is alles in orde..

[Janoz]

KabouterSuper schreef op maandag 07 januari 2008 @ 15:26:
UNION.... waarom moeilijk doen als het makkelijk kan?

Omdat de gegevens niet 'onder elkaar' moeten komen, maar 'naast elkaar'. Je wilt immers per week de aanmaak en wijzig aantallen hebben. Niet eerst alle weken met aanmaak hoeveelheden en daarna weer dezelfde weken met wijzig hoeveelheden.

[KabouterSuper]

goed punt....in dat geval kan je overwegen een kruistabel te maken die je sommeert

code:

SELECT YEAR, WEEK, SUM(COUNT_CREATED), SUM(COUNT_MODIFIED)
FROM
(
SELECT  
    Datename(YEAR, Date_Created) as YEAR,  
    Datename(WEEK, Date_Created) as WEEK,  
    COUNT (*) as COUNT_CREATED 
    0 as COUNT_MODIFIED
FROM  
   SomeTable 
GROUP BY 
    Datename(YEAR, Date_Created),  
    Datename(WEEK, Date_Created)
UNION
SELECT  
    Datename(YEAR, Date_Modified) as YEAR,  
    Datename(WEEK, Date_Modified) as WEEK,  
    0 AS COUNT_CREATED, 
   COUNT ( 
          CASE WHEN  
                 Date_Modified < DATEADD (hh, 12, Date_Created)  
          THEN  
                 NULL 
          ELSE 
                 Date_Modified 
          END) AS COUNT_MODIFIED 
FROM  
   SomeTable 
GROUP BY 
    Datename(YEAR, Date_Modified),  
    Datename(WEEK, Date_Modified)
)
GROUP BY 
    YEAR
    WEEK

Geen flauw idee of dit MSSQL-compatible is trouwens...in Oracle werkt het in elk geval prima.

[Verwijderd]

Dat geeft stof tot denken mensen! Bedankt voor de vele reacties (en zo snel al!) De bom, wat ik je zeg

Ik denk dat ik aan deze verzameling aan opties genoeg heb om voorlopig weer door te knutselen, mag ik nog vragen hebben, dan kom ik er nog op terug

Nogmaals bedankt!

[Verwijderd]

Ik wou effe iedereen bedanken voor de hulp. Uiteindelijk heb ik het voor elkaar gekregen. FULL JOIN heeft gewerkt. Ik had in het begin nog wat last van NULL resultaten op de jaar/week nummers waarop ik join, maar dat bleek met een slimme 'CASE WHEN variable IS NULL ' eenvoudig oplosbaar te zijn.

Voor degene die hetzelfde probleem hadden als ik en via de zoekfunctie hier terecht komen: de toegepaste query:

SQL:

SELECT  
    CASE WHEN CM_EDITED.YEAR IS NULL  THEN CM_CREATED.YEAR ELSE  CM_EDITED.YEAR END as YEAR , 
    CASE WHEN CM_EDITED.WEEK IS NULL THEN CM_CREATED.WEEK ELSE  CM_EDITED.WEEK END as WEEK, 
    CASE WHEN CM_CREATED.ITEM_CREATED IS NULL THEN 0 ELSE CM_CREATED.ITEM_CREATED END as ITEM_CREATED, 
    CASE WHEN CM_EDITED.ITEM_EDITED IS NULL THEN 0 ELSE CM_EDITED.ITEM_EDITED END as ITEM_EDITED
FROM
    (
    SELECT 
        ITEM_EDIT_YEAR as YEAR,
        ITEM_EDIT_WEEK as WEEK,
        COUNT(*) ITEM_EDITED
    FROM 
        SOMETABLE
    GROUP BY
        ITEM_EDIT_YEAR  ,
        ITEM_EDIT_WEEK
    ) CM_EDITED 
FULL OUTER JOIN 
    (
    SELECT 
        ITEM_CREAT_YEAR as YEAR,
        ITEM_CREAT_WEEK as WEEK,
        COUNT(*) ITEM_CREATED
    FROM 
        SOMETABLE
    GROUP BY
        ITEM_CREAT_YEAR,
        ITEM_CREAT_WEEK 
    ) CM_CREATED 
ON
    CM_CREATED.YEAR = CM_EDITED.YEAR AND
    CM_CREATED.WEEK = CM_EDITED.WEEK
ORDER BY 
    YEAR, WEEK
ASC