en-USsv-SE

Active Forums

PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 18 Oct 2010 03:29 PM by  JAhlen
SQLug.se challenge 2010
 65 Replies
Sort:
You are not authorized to post a reply.
Page 1 of 41234 > >>
Author Messages
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
12 Oct 2010 04:03 PM

    Nu drar årets svenska mästerskap för SQL Server utvecklare igång, arrangerat av SQLUG. Uppgiften är relativt enkel att komma igång med men svår att lösa optimalt.

    Tävlingen är både individuell och per företag. Som tävlande representerar man både sig själv som individ och det företag man jobbar på.

    Bästa individuella insats vinner ett presentkort på 990 kr hos Liveit, som kan användas till vin- och chokladprovning för två, prova stridsflygsimulator, spabehandling eller vad man nu föredrar.

    De fem bästa företagen belönas med varsin licens av SQL Source Control, som möjliggör källkodshantering i SQL Server på samma sätt som man hanterar programkod i t ex Visual Studio Team System, värde $295.

    Den största vinsten är naturligtvis äran att kunna titulera sig vinnare i SQLug.se challenge 2010!

    Tävlingsuppgift

    Vi har samlat in mätvärden från en bränslemätare hos ett antal fordon i en tabell som heter MeterReadings.


    ID

    Tid

    VehicleName

    MeterValue

    1

    2010-02-01 08:00:00.000

    LIFT1

    10000

    2

    2010-02-01 08:05:00.000

    LIFT1

    10025

    3

    2010-02-01 08:15:00.000

    LIFT1

    10065

    4

    2010-02-01 08:15:00.000

    LIFT2

    20000

    5

    2010-02-01 08:30:00.000

    LIFT2

    20150

    6

    2010-02-01 09:05:00.000

    LIFT1

    10315

    7

    2010-02-01 09:10:00.000

    LIFT2

    20510

    8

    2010-02-01 09:55:00.000

    LIFT2

    20810

    9

    2010-02-01 10:00:00.000

    LIFT1

    10480

    10

    2010-02-01 10:00:00.000

    LIFT2

    20885

    11

    2010-02-01 10:10:00.000

    LIFT1

    10530

    12

    2010-02-01 10:15:00.000

    LIFT1

    10575

    13

    2010-02-01 11:00:00.000

    LIFT1

    10725

    14

    2010-02-01 11:05:00.000

    LIFT2

    21445

    15

    2010-02-01 11:05:00.000

    LIFT1

    10755

    16

    2010-02-01 11:50:00.000

    LIFT1

    11295

    17

    2010-02-01 12:00:00.000

    LIFT1

    11325


    Man kan beräkna förbrukningen genom att ta differensen mellan två avlästa värden. T ex hade LIFT1 en förbrukning på 25 cl mellan kl 08:00 och 08:05. Förbrukningen var då 5 cl per minut.

    Uppgiften är att beräkna den tidsviktade genomsnittliga förbrukningen, i centiliter per minut, för de 20% perioder som hade den högsta förbrukningen/minut (avrunda antal perioder uppåt till närmaste heltal så det blir t ex 3 perioder om det totalt finns 11 perioder). Om flera perioder har samma förbrukning/minut, så välj de senaste perioderna.

    Se exempel nedan för LIFT1.

    VehicleName

    StartTime

    EndTime

    Consumption

    TimeInMinutes

    ConsumptionPerMinute

    LIFT1

    08:00:00

    08:05:00

    25

    5

    5

    LIFT1

    08:05:00

    08:15:00

    40

    10

    4

    LIFT1

    08:15:00

    09:05:00

    250

    50

    5

    LIFT1

    09:05:00

    10:00:00

    165

    55

    3

    LIFT1

    10:00:00

    10:10:00

    50

    10

    5

    LIFT1

    10:10:00

    10:15:00

    45

    5

    9

    LIFT1

    10:15:00

    11:00:00

    150

    45

    3,33333333333333

    LIFT1

    11:00:00

    11:05:00

    30

    5

    6

    LIFT1

    11:05:00

    11:50:00

    540

    45

    12

    LIFT1

    11:50:00

    12:00:00

    30

    10

    3

    Totalt har vi 10 perioder så 20% motsvarar två perioder. De två perioder med högst förbrukning hade 12 cl/minut respektive 9 cl/minut och de hade en varaktighet på 45 respektive 5 minuter.

    Den tidsviktade genomsnittliga förbrukningen för LIFT1 blir då:

    (12 x 45 + 9 x 5) / (45 + 5) = 11,7 cl/minut.

    Samma beräkning, fast för LIFT2 ger en genomsnittlig förbrukning på 15 cl/minut.

    Tävlingsuppgiften är att skriva snabbast möjliga T-SQL / SQL CLR lösning för att göra denna beräkning. Vi kommer att provköra era lösningar på indata bestående av cirka 1 miljon rader på en maskin som kör SQL Server 2008 R2 Enterprise Edition och har en quadcore CPU.

    Utresultatet skall skrivas till en tabell som heter ConsumptionSummary.

    VehicleName

    Top20PctAveragePerMinute

    LIFT1

    11,7

    LIFT2

    15


    Material


    Script för att skapa tabellerna: SQLUGChallenge2010Setup.txt
    Testdata (cirka 200 000 rader, slumpade): SQLUGChallenge2010.zip

    För att läsa in testdata, zippa upp på valfri plats och skriv
    BULK INSERT MeterReadings FROM 'Sökväg till filen' WITH (FIRSTROW = 2)

    Facit för dessa 200 000 rader.

    VehicleName Top20PctAveragePerMinute
    LIFT1 8202.18834892846
    LIFT2 27949.9640499379
    LIFT3 13310.5810794649
    LIFT4 19097.1263597138

    Regler

    • Tävlingen är öppen för alla SQLUGs medlemmar.
    • Tävlingsbidrag postas som svar på detta forum. De skall märkas med namn och företag.
    • Vinnare är den/de som har presterat den snabbaste lösningen.
    • Tillåtna metoder är T-SQL och SQL CLR. SQL CLR måste köras i safe läge och källkoden måste bifogas.
    • Inga schemaförändringar på befintliga tabeller är tillåtna förutom att skapa icke-klustrade index.
    • Det är tillåtet att skapa index, vyer, funktioner och procedurer (men ej indexerade vyer). Lösningarna måste städa upp efter sig så att den kan köras om och om igen. Posta din lösning uppdelad på tre script: Setup, Huvudkod, Uppstädning
    • Eventuell vinstskatt betalas av vinnarna.
    • Lösningarna kommer att testköras på cirka 1 miljon rader. Vi reserverar oss för möjligheten att öka antalet rader med testdata om det skulle krävas för att tävlingen skall bli utslagsgivande.
    • Tävlingen avgörs av en jury som består av Johan åhlén, Tibor Karaszi och Lars Utterström.
    • Deadline för att komma med tävlingsbidrag är 30 november kl 22:00 (svensk tid).


    Vinster

    Individuell tävling: Första pris är ett presentkort på 990 kr från liveit.se
    Företagstävling: Fem bästa lösningarna vinner en licens av SQL Source Control

    Hur du tävlar

    Posta din lösning på det här forumet. Skriv ditt namn och företagsnamn (om du också representerar ett företag/organisation).

    Frågor

    Frågor angående tävlingen ställs i detta forum.

    Lycka till!

    SwePeso
    New Member
    New Member
    Posts: 67


    --
    13 Oct 2010 04:09 PM
    Ingen som vågar börja?
    Steinar
    New Member
    New Member
    Posts: 13


    --
    18 Oct 2010 02:31 PM
    Hej!

    En fråga: när ni mäter tiden för lösningen, mäter ni då bara tiden för huvudscriptet, eller mäter ni även tiden för setup och uppstädningsscriptet?

    Mvh
    Steinar
    JAhlen
    Veteran Member
    Veteran Member
    Posts: 144


    --
    18 Oct 2010 03:29 PM
    Vi mäter bara tiden för huvudscriptet.

    Mvh
    Johan
    SwePeso
    New Member
    New Member
    Posts: 67


    --
    26 Oct 2010 01:43 PM
    Nu är det bara 4 dagar kvar...
    JAhlen
    Veteran Member
    Veteran Member
    Posts: 144


    --
    26 Oct 2010 01:52 PM
    Nej, tävlingen slutar 30:e november.
    SwePeso
    New Member
    New Member
    Posts: 67


    --
    26 Oct 2010 02:02 PM
    Så är det ju!

    Finns det någon som har en fungerade lösning överhuvudtaget, förutom mig?
    Henrik Nilsson
    New Member
    New Member
    Posts: 9


    --
    27 Oct 2010 01:29 AM
    är det ok att resultaten varierar några tiondelar upp och ner eller måste det vara exakt? Det känns som att det beror på flyttalsproblem...
    JAhlen
    Veteran Member
    Veteran Member
    Posts: 144


    --
    27 Oct 2010 06:34 AM
    Hej,

    Jag rekommenderar att du postar lösningen på vårt forum så får du våra testresultat.

    Flyttalsproblem bortser vi ifrån.

    MVH
    Johan
    Panos Koutsouris
    New Member
    New Member
    Posts: 1


    --
    27 Oct 2010 03:50 PM
    Hej!

    Jag har en lösning som får lite decimalfel och tyvärr så åker jag på semester om någon dag och hinner därför inte fixa mer med lösningen. Så jag tänkte att jag skickar in den ändå så kan jag läsa sen vad jag gjorde/tänkte fel. Hoppas det är ok.

    SELECT

    VehicleName

    ,CAST(SUM(ConsumptionPerMin*Duration)AS FLOAT)/CAST(SUM(Duration)AS FLOAT) AS Top20PctAveragePerMinute

    FROM(

    SELECT

    VehicleName

    ,Duration

    ,ConsumptionPerMin

    FROM(

    SELECT

    VehicleName

    ,Duration

    ,ConsumptionPerMin

    ,ROW_NUMBER() over (Partition BY VehicleName

    order by ConsumptionPerMin DESC) as Rank

    ,COUNT(*) over (Partition BY VehicleName) as TotalRank

    FROM(

    SELECT

    A.[VehicleName]

    ,CAST(DATEDIFF(ms,A.[StartTime],B.[StartTime])AS FLOAT) AS Duration

    ,CAST((B.[MeterValue]-A.[MeterValue])/(CAST(DATEDIFF(ms,A.[StartTime],B.[StartTime])AS FLOAT)/1000/60)AS FLOAT) AS ConsumptionPerMin

    FROM(

    SELECT

    [VehicleName]

    ,[StartTime]

    ,[MeterValue]

    ,ROW_NUMBER() OVER(PARTITION BY [VehicleName] ORDER BY [StartTime]ASC) AS 'RowNumber'

    FROM [Min].[dbo].[MeterReadings]

    ) A

    INNER JOIN (

    SELECT

    [VehicleName]

    ,[StartTime]

    ,[MeterValue]

    ,ROW_NUMBER() OVER(PARTITION BY [VehicleName] ORDER BY [StartTime] ASC) -1 AS 'RowNumber'

    FROM [Min].[dbo].[MeterReadings]

    ) B ON A.[VehicleName] = B.[VehicleName] AND A.[RowNumber] = B.[RowNumber]

    )RES

    )RES

    WHERE RANK <= (TotalRank*.2)

    )RES

    GROUP BY VehicleName

    ORDER BY 1

    Mvh
    Panos

    Henrik Nilsson
    New Member
    New Member
    Posts: 9


    --
    27 Oct 2010 09:41 PM
    är det inte bättre att maila in koden och att ni redovisar de olika bidragen samtidigt?
    SwePeso
    New Member
    New Member
    Posts: 67


    --
    27 Oct 2010 10:31 PM
    För min egen del föredrar jag öppna tävlingar, dvs att bidragen är publika och att den aktuella ställningen redovisas efterhand som bidragen kommer in. Det finns mest fördelar med detta.
    Nackdelen är att det inte blir en tävling i sitt eget ords bemärkelse. Men eftersom SQLUG.se samt PASS Scania (som jag representerar) i första hand handlar om "Connect, Share and Learn" så överväger fördelen med att kunna studera och ta till sig andras lösningar.
    Tänk om din lösning kör på 7 sekunder och någon annan postar en lösning som kör på 4 sekunder. Jag vet inte hur du reagerar men jag skulle studera den noggrant för att se dels vad jag själv inte tänkte på, dels eventuellt lära mig något nytt och kanske till och med förbättra den snabbare lösningen.
    Uppenbara plagiat kommer Johan att genomskåda om den inte tillför något nytt och kommer då att vara utan tävlan.

    //Peter
    JAhlen
    Veteran Member
    Veteran Member
    Posts: 144


    --
    28 Oct 2010 07:35 PM
    Hej Panos,

    Tack för ditt tävlingsbidrag! Jag har testat din lösning och fått följande resultat:

    Exekveringstid: 2,75 s

    (dessutom lite statistik som kan vara intressant - CPU-tid: 8,25 s och antal reads: 455419)

    Tyvärr stämmer inte resultatet riktigt (även om du är nära). Ett tips är att kolla igenom noga vilka perioder som räknas med.

    När fler tävlingsbidrag kommer in så kommer vi att publicera en "high-score" lista.

    Lycka till och kom ihåg att tävlingen pågår till den 30:e november.

    MVH
    Johan
    JAhlen
    Veteran Member
    Veteran Member
    Posts: 144


    --
    28 Oct 2010 07:47 PM
    Henge skrev:
    är det inte bättre att maila in koden och att ni redovisar de olika bidragen samtidigt?

    Nej, vi har valt att köra en öppen tävling eftersom syftet är att göra detta till en lärande aktivitet. Men det är inget som hindrar att du postar din lösning i sista minuten om du vill hålla den hemlig.

    MVH
    Johan

    Ola Hallengren
    New Member
    New Member
    Posts: 2


    --
    06 Nov 2010 01:25 AM
    Hej,

    Jag har en lösning med en CTE och lite ranking funktioner.

    Först så ska man skapa det här indexet.

    CREATE NONCLUSTERED INDEX IX_VehicleName_StartTime ON dbo.MeterReadings (VehicleName ASC, StartTime ASC) INCLUDE (MeterValue)

    Sedan så är scriptet här.

    WITH MeterReadingsRowNumber AS (

    SELECT VehicleName,
    ROW_NUMBER() OVER (PARTITION BY VehicleName ORDER BY StartTime ASC) AS RowNumber,
    StartTime,
    MeterValue
    FROM MeterReadings

    ),

    Periods AS (

    SELECT MeterReadingsRowNumber1.VehicleName,
    MeterReadingsRowNumber1.StartTime StartTime,
    MeterReadingsRowNumber2.StartTime EndTime,
    CAST(MeterReadingsRowNumber2.MeterValue - MeterReadingsRowNumber1.MeterValue AS float) AS Consumption,
    CAST(DATEDIFF(ms,MeterReadingsRowNumber1.StartTime,MeterReadingsRowNumber2.StartTime) AS float) / 1000 / 60 AS TimeInMinutes,
    CAST(MeterReadingsRowNumber2.MeterValue - MeterReadingsRowNumber1.MeterValue AS float) / DATEDIFF(ms,MeterReadingsRowNumber1.StartTime,MeterReadingsRowNumber2.StartTime) * 1000 * 60 AS ConsumptionPerMinute
    FROM MeterReadingsRowNumber MeterReadingsRowNumber1
    INNER JOIN MeterReadingsRowNumber MeterReadingsRowNumber2
    ON MeterReadingsRowNumber1.VehicleName = MeterReadingsRowNumber2.VehicleName
    AND MeterReadingsRowNumber1.RowNumber = MeterReadingsRowNumber2.RowNumber -1

    ),

    PeriodsPercentile AS (

    SELECT VehicleName,
    StartTime,
    EndTime,
    Consumption,
    TimeInMinutes,
    ConsumptionPerMinute,
    NTILE(100) OVER (PARTITION BY VehicleName ORDER BY ConsumptionPerMinute DESC, StartTime DESC) AS Percentile
    FROM Periods

    )

    MERGE INTO ConsumptionSummary AS TARGET
    USING
    (

    SELECT VehicleName, SUM(Consumption) / SUM(TimeInMinutes) AS Top20PctAveragePerMinute
    FROM PeriodsPercentile
    WHERE Percentile <= 20
    GROUP BY VehicleName

    ) AS SOURCE
    ON TARGET.VehicleName = SOURCE.VehicleName

    WHEN MATCHED THEN UPDATE SET Top20PctAveragePerMinute = SOURCE.Top20PctAveragePerMinute
    WHEN NOT MATCHED BY TARGET THEN INSERT (VehicleName, Top20PctAveragePerMinute) VALUES (VehicleName, Top20PctAveragePerMinute)
    WHEN NOT MATCHED BY SOURCE THEN DELETE;

    Det tar ca 3 sekunder på min dator.

    M.v.h.

    Ola
    Sergey
    New Member
    New Member
    Posts: 16


    --
    07 Nov 2010 10:11 PM
    Hej!

    Här följer min lösning:


    -- SETUP
    CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
    (
    [VehicleName] ASC,
    [StartTime] ASC
    )
    INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
    GO
    truncate table dbo.[ConsumptionSummary]
    GO


    -- MAIN RUN

    ;with [Ordered List] as (
    SELECT
    row_number() over(partition by [VehicleName] order by starttime) rn
    ,[StartTime]
    ,[VehicleName]
    ,[MeterValue]
    FROM [dbo].[MeterReadings]

    ), [Consumption Stats] as (
    select
    t1.[VehicleName]
    ,t2.[MeterValue] - t1.[MeterValue] m
    ,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
    from [Ordered List] t1
    join [Ordered List] t2
    on t1.[VehicleName]=t2.[VehicleName]
    and t1.rn + 1 = t2.rn

    ), [20% Slices] as (
    select NTILE(5) over(
    partition by [VehicleName]
    order by (convert(float,m)/ms) desc -- consumption/ms
    ) as rn
    ,[VehicleName],m,ms
    from [Consumption Stats]
    )

    INSERT INTO [dbo].[ConsumptionSummary]
    ([VehicleName]
    ,[Top20PctAveragePerMinute])
    output inserted.*
    select [VehicleName]
    ,(convert(float,(sum(m)))/sum(ms)) * 60000
    from [20% Slices]
    where rn = 1 -- first 20%
    group by [VehicleName];

    GO

    -- CLEAN UP
    drop index ix_name ON [dbo].[MeterReadings]
    GO
    truncate table [ConsumptionSummary]
    GO


    JAhlen
    Veteran Member
    Veteran Member
    Posts: 144


    --
    08 Nov 2010 03:06 PM
    olahallengren skrev:
    Hej,

    Jag har en lösning med en CTE och lite ranking funktioner.

    Först så ska man skapa det här indexet.

    CREATE NONCLUSTERED INDEX IX_VehicleName_StartTime ON dbo.MeterReadings (VehicleName ASC, StartTime ASC) INCLUDE (MeterValue)

    Sedan så är scriptet här.

    WITH MeterReadingsRowNumber AS (

    SELECT VehicleName,
    ROW_NUMBER() OVER (PARTITION BY VehicleName ORDER BY StartTime ASC) AS RowNumber,
    StartTime,
    MeterValue
    FROM MeterReadings

    ),

    Periods AS (

    SELECT MeterReadingsRowNumber1.VehicleName,
    MeterReadingsRowNumber1.StartTime StartTime,
    MeterReadingsRowNumber2.StartTime EndTime,
    CAST(MeterReadingsRowNumber2.MeterValue - MeterReadingsRowNumber1.MeterValue AS float) AS Consumption,
    CAST(DATEDIFF(ms,MeterReadingsRowNumber1.StartTime,MeterReadingsRowNumber2.StartTime) AS float) / 1000 / 60 AS TimeInMinutes,
    CAST(MeterReadingsRowNumber2.MeterValue - MeterReadingsRowNumber1.MeterValue AS float) / DATEDIFF(ms,MeterReadingsRowNumber1.StartTime,MeterReadingsRowNumber2.StartTime) * 1000 * 60 AS ConsumptionPerMinute
    FROM MeterReadingsRowNumber MeterReadingsRowNumber1
    INNER JOIN MeterReadingsRowNumber MeterReadingsRowNumber2
    ON MeterReadingsRowNumber1.VehicleName = MeterReadingsRowNumber2.VehicleName
    AND MeterReadingsRowNumber1.RowNumber = MeterReadingsRowNumber2.RowNumber -1

    ),

    PeriodsPercentile AS (

    SELECT VehicleName,
    StartTime,
    EndTime,
    Consumption,
    TimeInMinutes,
    ConsumptionPerMinute,
    NTILE(100) OVER (PARTITION BY VehicleName ORDER BY ConsumptionPerMinute DESC, StartTime DESC) AS Percentile
    FROM Periods

    )

    MERGE INTO ConsumptionSummary AS TARGET
    USING
    (

    SELECT VehicleName, SUM(Consumption) / SUM(TimeInMinutes) AS Top20PctAveragePerMinute
    FROM PeriodsPercentile
    WHERE Percentile <= 20
    GROUP BY VehicleName

    ) AS SOURCE
    ON TARGET.VehicleName = SOURCE.VehicleName

    WHEN MATCHED THEN UPDATE SET Top20PctAveragePerMinute = SOURCE.Top20PctAveragePerMinute
    WHEN NOT MATCHED BY TARGET THEN INSERT (VehicleName, Top20PctAveragePerMinute) VALUES (VehicleName, Top20PctAveragePerMinute)
    WHEN NOT MATCHED BY SOURCE THEN DELETE;

    Det tar ca 3 sekunder på min dator.

    M.v.h.

    Ola

    Hej Ola,

    Resultat för ditt tävlingsbidrag:
    Utresultat GODKäNT
    Exekveringstid 2,150 s
    CPU-tid 3,650 s
    Antal reads 468 015

    Mvh
    Johan

    JAhlen
    Veteran Member
    Veteran Member
    Posts: 144


    --
    08 Nov 2010 03:14 PM
    seregak76 skrev:
    Hej!

    Här följer min lösning:


    -- SETUP
    CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
    (
    [VehicleName] ASC,
    [StartTime] ASC
    )
    INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
    GO
    truncate table dbo.[ConsumptionSummary]
    GO


    -- MAIN RUN

    ;with [Ordered List] as (
    SELECT
    row_number() over(partition by [VehicleName] order by starttime) rn
    ,[StartTime]
    ,[VehicleName]
    ,[MeterValue]
    FROM [dbo].[MeterReadings]

    ), [Consumption Stats] as (
    select
    t1.[VehicleName]
    ,t2.[MeterValue] - t1.[MeterValue] m
    ,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
    from [Ordered List] t1
    join [Ordered List] t2
    on t1.[VehicleName]=t2.[VehicleName]
    and t1.rn + 1 = t2.rn

    ), [20% Slices] as (
    select NTILE(5) over(
    partition by [VehicleName]
    order by (convert(float,m)/ms) desc -- consumption/ms
    ) as rn
    ,[VehicleName],m,ms
    from [Consumption Stats]
    )

    INSERT INTO [dbo].[ConsumptionSummary]
    ([VehicleName]
    ,[Top20PctAveragePerMinute])
    output inserted.*
    select [VehicleName]
    ,(convert(float,(sum(m)))/sum(ms)) * 60000
    from [20% Slices]
    where rn = 1 -- first 20%
    group by [VehicleName];

    GO

    -- CLEAN UP
    drop index ix_name ON [dbo].[MeterReadings]
    GO
    truncate table [ConsumptionSummary]
    GO



    Hej,

    Resultat för ditt tävlingsbidrag:
    Utresultat EJ GODKäNT
    Exekveringstid 2,229 s
    CPU-tid 2,153 s
    Antal reads 468 018

    Utresultatet stämmer nästan (och det beror inte på avrundningsfel).

    Mvh
    Johan

    Sergey
    New Member
    New Member
    Posts: 16


    --
    09 Nov 2010 09:34 AM
    Man får väl läsa uppgiften noga :)

    Här följer korrigerat variant:

    -- SETUP
    CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
    (
    [VehicleName] ASC,
    [StartTime] ASC
    )
    INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
    GO
    truncate table dbo.[ConsumptionSummary]
    GO


    -- MAIN RUN

    ;with [Ordered List] as (
    SELECT
    row_number() over(partition by [VehicleName] order by starttime) rn
    ,[StartTime]
    ,[VehicleName]
    ,[MeterValue]
    FROM [dbo].[MeterReadings]

    ), [Consumption Stats] as (
    select
    t1.[VehicleName]
    ,t1.[StartTime]
    ,t2.[MeterValue] - t1.[MeterValue] m
    ,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
    from [Ordered List] t1
    join [Ordered List] t2
    on t1.[VehicleName]=t2.[VehicleName]
    and t1.rn + 1 = t2.rn

    ), [20% Slices] as (
    select NTILE(5) over(
    partition by [VehicleName]
    order by (convert(float,m)/ms), [StartTime] -- consumption/ms, time
    ) as rn
    ,[VehicleName],m,ms
    from [Consumption Stats]
    )

    INSERT INTO [dbo].[ConsumptionSummary]
    ([VehicleName]
    ,[Top20PctAveragePerMinute])
    output inserted.*
    select [VehicleName]
    ,(convert(float,(sum(m)))/sum(ms)) * 60000
    from [20% Slices]
    where rn = 5 -- last 20%
    group by [VehicleName];

    GO

    -- CLEAN UP
    drop index ix_name ON [dbo].[MeterReadings]
    GO
    truncate table [ConsumptionSummary]
    GO
    JAhlen
    Veteran Member
    Veteran Member
    Posts: 144


    --
    09 Nov 2010 09:50 AM
    seregak76 skrev:
    Man får väl läsa uppgiften noga :)

    Här följer korrigerat variant:

    -- SETUP
    CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
    (
    [VehicleName] ASC,
    [StartTime] ASC
    )
    INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
    GO
    truncate table dbo.[ConsumptionSummary]
    GO


    -- MAIN RUN

    ;with [Ordered List] as (
    SELECT
    row_number() over(partition by [VehicleName] order by starttime) rn
    ,[StartTime]
    ,[VehicleName]
    ,[MeterValue]
    FROM [dbo].[MeterReadings]

    ), [Consumption Stats] as (
    select
    t1.[VehicleName]
    ,t1.[StartTime]
    ,t2.[MeterValue] - t1.[MeterValue] m
    ,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
    from [Ordered List] t1
    join [Ordered List] t2
    on t1.[VehicleName]=t2.[VehicleName]
    and t1.rn + 1 = t2.rn

    ), [20% Slices] as (
    select NTILE(5) over(
    partition by [VehicleName]
    order by (convert(float,m)/ms), [StartTime] -- consumption/ms, time
    ) as rn
    ,[VehicleName],m,ms
    from [Consumption Stats]
    )

    INSERT INTO [dbo].[ConsumptionSummary]
    ([VehicleName]
    ,[Top20PctAveragePerMinute])
    output inserted.*
    select [VehicleName]
    ,(convert(float,(sum(m)))/sum(ms)) * 60000
    from [20% Slices]
    where rn = 5 -- last 20%
    group by [VehicleName];

    GO

    -- CLEAN UP
    drop index ix_name ON [dbo].[MeterReadings]
    GO
    truncate table [ConsumptionSummary]
    GO

    Nu stämde det.

    Resultat för ditt tävlingsbidrag:
    Utresultat GODKäNT
    Exekveringstid 2,229 s
    CPU-tid 2,153 s
    Antal reads 468 018

    Mvh
    Johan

    You are not authorized to post a reply.
    Page 1 of 41234 > >>