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 3 of 4 << < 1234 > >>
Author Messages
kewin
New Member
New Member
Posts: 4


--
25 Nov 2010 10:47 AM
En liten finjustering, kanske ev kan kapa någon ms...

/******************************************
Kenneth Wilhelmsson
Cybernetics AB
Edition 1, version 2
******************************************/
-- setup
create unique nonclustered index VehicleName_StartTime_uix
on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue) with (fillfactor = 100)
create nonclustered index VehicleName_ix
on dbo.MeterReadings(VehicleName asc) with (fillfactor = 100)
go
truncate table dbo.ConsumptionSummary
go

declare @start datetime, @end datetime
set @start = getdate()


-- Main run
set nocount on
declare @VehicleName varchar(50)

declare @c table ( VehicleName varchar(50) not null, cnt int not null )
insert @c select VehicleName, (count(*) - 1) * 0.2 from dbo.MeterReadings group by VehicleName


while exists ( select * from @c )
begin
select @VehicleName = ( select top 1 VehicleName from @c )

insert dbo.ConsumptionSummary ( VehicleName, Top20PctAveragePerMinute )
select z.VehicleName,
((sum(z.conPerUnit * z.duration) / sum(z.duration)) * 1000 * 60) as Top20PctAveragePerMinute
from (
select i.VehicleName,
datediff(ms, u.StartTime, i.StartTime) as duration,
(cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) as conPerUnit,
row_number() over
( order by (cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) desc, i.StartTime desc ) as srow
from (
select row_number() over ( order by i.VehicleName asc, i.StartTime desc) as i_rownum,
i.VehicleName, i.StartTime, i.MeterValue
from dbo.MeterReadings i
where i.VehicleName = @VehicleName
) i
join
(
select row_number() over ( order by u.VehicleName asc, u.StartTime desc) as u_rownum,
u.VehicleName, u.StartTime, u.MeterValue
from dbo.MeterReadings u
where u.VehicleName = @VehicleName
) u
on i.i_rownum = u.u_rownum - 1
) z
join @c c
on z.srow <= c.cnt
and z.VehicleName = c.VehicleName
group by z.VehicleName

delete @c where VehicleName = @VehicleName
end
go

-- cleanup
drop index MeterReadings.VehicleName_StartTime_uix
drop index MeterReadings.VehicleName_ix
set nocount off
go
kewin
New Member
New Member
Posts: 4


--
25 Nov 2010 11:07 AM
Hur många varianter får man komma med?
Här är en helt annan approach - ser ut att vara väldigt få reads, men ganska dyr i CPU.
(på min stackars desktop iaf)
Vore intressant att se hur väl den kan skala...

/******************************************
Kenneth Wilhelmsson
Cybernetics AB
Edition 2, version 1
******************************************/
-- setup
create unique nonclustered index VehicleName_StartTime_uix
on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue) with (fillfactor = 100)
create nonclustered index VehicleName_ix
on dbo.MeterReadings(VehicleName asc) with (fillfactor = 100)
go
create type VehicleCount as table
( VehicleName varchar(50) not null, cnt int not null );
go
truncate table dbo.ConsumptionSummary
go


-- Main run
set nocount on
declare @c as VehicleCount;


insert @c ( VehicleName, cnt )
select VehicleName, (count(*) - 1) * 0.2
from dbo.MeterReadings
group by VehicleName;

insert dbo.ConsumptionSummary ( VehicleName, Top20PctAveragePerMinute )
select z.VehicleName,
((sum(z.conPerUnit * z.duration) / sum(z.duration)) * 1000 * 60) as Top20PctAveragePerMinute
from (
select i.VehicleName,
datediff(ms, u.StartTime, i.StartTime) as duration,
(cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) as conPerUnit,
row_number() over
( partition by i.VehicleName order by (cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) desc, i.StartTime desc ) as srow
from (
select row_number() over ( partition by i.VehicleName order by i.VehicleName asc, i.StartTime desc) as i_rownum,
i.VehicleName, i.StartTime, i.MeterValue
from dbo.MeterReadings i
) i
join
(
select row_number() over ( partition by u.VehicleName order by u.VehicleName asc, u.StartTime desc) as u_rownum,
u.VehicleName, u.StartTime, u.MeterValue
from dbo.MeterReadings u
) u
on i.i_rownum = u.u_rownum - 1
and i.VehicleName = u.VehicleName
join @c c
on i.VehicleName = c.VehicleName
) z
join @c c
on z.srow <= c.cnt
and z.VehicleName = c.VehicleName
where z.srow is not null
group by z.VehicleName;
go

-- cleanup
drop index MeterReadings.VehicleName_StartTime_uix
drop index MeterReadings.VehicleName_ix
drop type VehicleCount
set nocount off
go
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
25 Nov 2010 03:34 PM
kewin skrev:
Hur många varianter får man komma med?
Här är en helt annan approach - ser ut att vara väldigt få reads, men ganska dyr i CPU.
(på min stackars desktop iaf)
Vore intressant att se hur väl den kan skala...

/******************************************
Kenneth Wilhelmsson
Cybernetics AB
Edition 2, version 1
******************************************/
-- setup
create unique nonclustered index VehicleName_StartTime_uix
on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue) with (fillfactor = 100)
create nonclustered index VehicleName_ix
on dbo.MeterReadings(VehicleName asc) with (fillfactor = 100)
go
create type VehicleCount as table
( VehicleName varchar(50) not null, cnt int not null );
go
truncate table dbo.ConsumptionSummary
go


-- Main run
set nocount on
declare @c as VehicleCount;


insert @c ( VehicleName, cnt )
select VehicleName, (count(*) - 1) * 0.2
from dbo.MeterReadings
group by VehicleName;

insert dbo.ConsumptionSummary ( VehicleName, Top20PctAveragePerMinute )
select z.VehicleName,
((sum(z.conPerUnit * z.duration) / sum(z.duration)) * 1000 * 60) as Top20PctAveragePerMinute
from (
select i.VehicleName,
datediff(ms, u.StartTime, i.StartTime) as duration,
(cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) as conPerUnit,
row_number() over
( partition by i.VehicleName order by (cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) desc, i.StartTime desc ) as srow
from (
select row_number() over ( partition by i.VehicleName order by i.VehicleName asc, i.StartTime desc) as i_rownum,
i.VehicleName, i.StartTime, i.MeterValue
from dbo.MeterReadings i
) i
join
(
select row_number() over ( partition by u.VehicleName order by u.VehicleName asc, u.StartTime desc) as u_rownum,
u.VehicleName, u.StartTime, u.MeterValue
from dbo.MeterReadings u
) u
on i.i_rownum = u.u_rownum - 1
and i.VehicleName = u.VehicleName
join @c c
on i.VehicleName = c.VehicleName
) z
join @c c
on z.srow <= c.cnt
and z.VehicleName = c.VehicleName
where z.srow is not null
group by z.VehicleName;
go

-- cleanup
drop index MeterReadings.VehicleName_StartTime_uix
drop index MeterReadings.VehicleName_ix
drop type VehicleCount
set nocount off
go

Vi har inte satt någon begränsning för antalet tävlingsbidrag. Kommer du på något bättre så är det bara att skicka in.

Exekveringstid för denna lösning: 1,85 s
CPU-tid: 3,08 s

Mvh
Johan

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
25 Nov 2010 03:55 PM
Ett förtydligande:
Om ni skickar in flera lösningar så kommer vi att välja den av era lösningar som presterar bäst.

MVH
Johan
Zhong
New Member
New Member
Posts: 11


--
25 Nov 2010 07:21 PM

Här är mitt bidrag till tävlingen.

Zhong Yu

B3IT



-- Setup
create unique nonclustered index uidx_MeterReadings_VehicleName_StartTime
on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue)
with (fillfactor = 100);


truncate table ConsumptionSummary

-- Main
declare cur cursor for
select ceiling(cast((COUNT(*)-1) as decimal)/5), VehicleName
from MeterReadings group by VehicleName;

declare @p20 int, @vehicle varchar(50)
open cur

fetch next from cur into @p20, @vehicle

while @@FETCH_STATUS = 0
begin
with T as (
select rownbr = ROW_NUMBER() over (order by StartTime asc), StartTime, MeterValue
from MeterReadings
where VehicleName = @vehicle
)
insert into ConsumptionSummary (VehicleName, Top20PctAveragePerMinute)
select @vehicle, cast(SUM(cl) as float)/SUM(ms) * 60000
from (
select top(@p20) cl = T2.MeterValue - T1.MeterValue, ms = DATEDIFF(ms, T1.StartTime, T2.StartTime)
from T as T1 Join T as T2 on T1.rownbr = T2.rownbr - 1
order by cast((T2.MeterValue - T1.MeterValue) as decimal)/cast(DATEDIFF(ms, T1.StartTime, T2.StartTime) as decimal) desc, T1.StartTime desc
) S

fetch next from cur into @p20, @vehicle

end
close cur
deallocate cur

-- Cleanup
drop index uidx_MeterReadings_VehicleName_StartTime on dbo.MeterReadings
martin.johansson
New Member
New Member
Posts: 13


--
26 Nov 2010 08:44 PM

Jag får väll slänga in en lösning jag med.
Försökte hålla den så läsbar som möjligt

Håll tillgodo

/*********************

* Martin Johansson *

* Elib AB *

*********************/

use SQLSM2010

-- setup

-- unikt index ver VehicleName och StartTime

create unique nonclustered index VehicleName_StartTime_uix

on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue) with (fillfactor = 100)

-- indexering av VehicleName

create nonclustered index VehicleName_ix

on dbo.MeterReadings(VehicleName asc) with (fillfactor = 100)

go

-- rensa tabellen ConsumptionSummary

truncate table dbo.ConsumptionSummary

go

USE [SQLSM2010]

GO

-- Fr att falla fr grupptrycket

set nocount on

go

-- Main run

-- Cursor för att hantera antalet rader (Alternativt skulle man kunna göra ngon slags set based iteration men det skulle inte sticka tillräckligt i folks ögon :)

declare crs_LiftCount cursor

for

select (MAX(a.RowID)-1)*0.2, a.VehicleName

from (select ROW_NUMBER() over (partition by vehiclename order by vehiclename, starttime) as RowID, VehicleName, StartTime, MeterReadings.MeterValue from dbo.MeterReadings) as a

group by vehiclename

declare @lccC int -- Hller antalet rader

declare @lccN varchar(50) -- Hller VehicleName

open crs_LiftCount

fetch next from crs_LiftCount into @lccC, @LccN

WHILE @@FETCH_STATUS = 0

BEGIN

-- CTE med aggregerade funktioner fr lsbarhet

WITH MeterReadingsRowID(rowID, vehicleName,startTime, meterValue) AS

(

select ROW_NUMBER() over (partition by vehiclename order by vehiclename, starttime) as rownum,

VehicleName,

StartTime,

MeterValue

from dbo.MeterReadings

Where vehiclename=@lccN

), ConsumptionTop20(VehicleName, StartTime, EndTime, MilliSeconds, Consumption, ConsumptionPerMinute) as

(

select top(@lccC) a.VehicleName, a.StartTime, b.StartTime, DATEDIFF(MILLISECOND, b.starttime, a.starttime), a.MeterValue - b.MeterValue, CAST(b.MeterValue - a.MeterValue AS float) / CAST(DATEDIFF(millisecond, a.StartTime, b.StartTime) AS float) * 60000 AS cons_per_min

from MeterReadingsRowID a inner join MeterReadingsRowID b on a.rowID= b.rowID +1

order by cons_per_min desc, a.starttime desc

)

Insert Into ConsumptionSummary

select VehicleName, 60000 * Cast(SUM(consumption) as float)/cast(SUM(milliseconds) as float) as WeightedConsumptionPerMinute

from ConsumptionTop20

group by vehiclename;

fetch next from crs_LiftCount into @lccC, @LccN

END

close crs_LiftCount

deallocate crs_LiftCount

GO

-- cleanup

-- Ta bort unikt index ver Vehiclename och StartTime

drop index MeterReadings.VehicleName_StartTime_uix

-- Ta bort index ver VehicleName

drop index MeterReadings.VehicleName_ix

set nocount off

go

Zhong
New Member
New Member
Posts: 11


--
26 Nov 2010 10:20 PM
Nästan samma kod som förra, men lite snyggare.



-- Setup
create unique nonclustered index uidx_MeterReadings_VehicleName_StartTime
on dbo.MeterReadings(VehicleName asc, StartTime asc) include (MeterValue)
with (fillfactor = 100);

truncate table ConsumptionSummary


-- Main
declare cur cursor for
select ceiling( 0.2 * (COUNT(*)-1) ), VehicleName
from MeterReadings
group by VehicleName;

declare @p20 int, @vehicle varchar(50)
open cur

fetch next from cur into @p20, @vehicle

while @@FETCH_STATUS = 0
begin
with T as (
select rownbr = ROW_NUMBER() over (order by StartTime asc), StartTime, MeterValue
from MeterReadings
where VehicleName = @vehicle
)
insert into ConsumptionSummary (VehicleName, Top20PctAveragePerMinute)
select @vehicle, cast(SUM(cl) as float)/cast(SUM(ms) as float) * 60000
from (
select top(@p20) cl = T2.MeterValue - T1.MeterValue, ms = DATEDIFF(ms, T1.StartTime, T2.StartTime)
from T as T1 Join T as T2 on T1.rownbr = T2.rownbr - 1
order by cast((T2.MeterValue - T1.MeterValue) as float)/cast(DATEDIFF(ms, T1.StartTime, T2.StartTime) as float) desc, T1.StartTime desc
) S

fetch next from cur into @p20, @vehicle

end
close cur
deallocate cur

-- Cleanup
drop index uidx_MeterReadings_VehicleName_StartTime on dbo.MeterReadings

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
27 Nov 2010 07:43 AM
Zhong skrev:
Nästan samma kod som förra, men lite snyggare.



-- Setup
create unique nonclustered index uidx_MeterReadings_VehicleName_StartTime
on dbo.MeterReadings(VehicleName asc, StartTime asc) include (MeterValue)
with (fillfactor = 100);

truncate table ConsumptionSummary


-- Main
declare cur cursor for
select ceiling( 0.2 * (COUNT(*)-1) ), VehicleName
from MeterReadings
group by VehicleName;

declare @p20 int, @vehicle varchar(50)
open cur

fetch next from cur into @p20, @vehicle

while @@FETCH_STATUS = 0
begin
with T as (
select rownbr = ROW_NUMBER() over (order by StartTime asc), StartTime, MeterValue
from MeterReadings
where VehicleName = @vehicle
)
insert into ConsumptionSummary (VehicleName, Top20PctAveragePerMinute)
select @vehicle, cast(SUM(cl) as float)/cast(SUM(ms) as float) * 60000
from (
select top(@p20) cl = T2.MeterValue - T1.MeterValue, ms = DATEDIFF(ms, T1.StartTime, T2.StartTime)
from T as T1 Join T as T2 on T1.rownbr = T2.rownbr - 1
order by cast((T2.MeterValue - T1.MeterValue) as float)/cast(DATEDIFF(ms, T1.StartTime, T2.StartTime) as float) desc, T1.StartTime desc
) S

fetch next from cur into @p20, @vehicle

end
close cur
deallocate cur

-- Cleanup
drop index uidx_MeterReadings_VehicleName_StartTime on dbo.MeterReadings


Exekveringstid: 0,48s
CPU-tid: 1,09 s

Utresultat: godkänt

MVH
Johan

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
27 Nov 2010 07:49 AM
martin.johansson skrev:

Jag får väll slänga in en lösning jag med.
Försökte hålla den så läsbar som möjligt

Håll tillgodo

/*********************

* Martin Johansson *

* Elib AB *

*********************/

use SQLSM2010

-- setup

-- unikt index ver VehicleName och StartTime

create unique nonclustered index VehicleName_StartTime_uix

on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue) with (fillfactor = 100)

-- indexering av VehicleName

create nonclustered index VehicleName_ix

on dbo.MeterReadings(VehicleName asc) with (fillfactor = 100)

go

-- rensa tabellen ConsumptionSummary

truncate table dbo.ConsumptionSummary

go

USE [SQLSM2010]

GO

-- Fr att falla fr grupptrycket

set nocount on

go

-- Main run

-- Cursor för att hantera antalet rader (Alternativt skulle man kunna göra ngon slags set based iteration men det skulle inte sticka tillräckligt i folks ögon :)

declare crs_LiftCount cursor

for

select (MAX(a.RowID)-1)*0.2, a.VehicleName

from (select ROW_NUMBER() over (partition by vehiclename order by vehiclename, starttime) as RowID, VehicleName, StartTime, MeterReadings.MeterValue from dbo.MeterReadings) as a

group by vehiclename

declare @lccC int -- Hller antalet rader

declare @lccN varchar(50) -- Hller VehicleName

open crs_LiftCount

fetch next from crs_LiftCount into @lccC, @LccN

WHILE @@FETCH_STATUS = 0

BEGIN

-- CTE med aggregerade funktioner fr lsbarhet

WITH MeterReadingsRowID(rowID, vehicleName,startTime, meterValue) AS

(

select ROW_NUMBER() over (partition by vehiclename order by vehiclename, starttime) as rownum,

VehicleName,

StartTime,

MeterValue

from dbo.MeterReadings

Where vehiclename=@lccN

), ConsumptionTop20(VehicleName, StartTime, EndTime, MilliSeconds, Consumption, ConsumptionPerMinute) as

(

select top(@lccC) a.VehicleName, a.StartTime, b.StartTime, DATEDIFF(MILLISECOND, b.starttime, a.starttime), a.MeterValue - b.MeterValue, CAST(b.MeterValue - a.MeterValue AS float) / CAST(DATEDIFF(millisecond, a.StartTime, b.StartTime) AS float) * 60000 AS cons_per_min

from MeterReadingsRowID a inner join MeterReadingsRowID b on a.rowID= b.rowID +1

order by cons_per_min desc, a.starttime desc

)

Insert Into ConsumptionSummary

select VehicleName, 60000 * Cast(SUM(consumption) as float)/cast(SUM(milliseconds) as float) as WeightedConsumptionPerMinute

from ConsumptionTop20

group by vehiclename;

fetch next from crs_LiftCount into @lccC, @LccN

END

close crs_LiftCount

deallocate crs_LiftCount

GO

-- cleanup

-- Ta bort unikt index ver Vehiclename och StartTime

drop index MeterReadings.VehicleName_StartTime_uix

-- Ta bort index ver VehicleName

drop index MeterReadings.VehicleName_ix

set nocount off

go

Exekveringstid: 1,05 s
CPU-tid: 2,50 s

Utresultat: godkänt

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
27 Nov 2010 08:04 AM

Preliminär ställning:

***************************************************************
* Namn * Företag * Tid *
***************************************************************
* Zhong Yu * B3IT * 0,48 s *
* Kenneth Wilhelmsson * Cybernetics AB * 0,98 s *
* Martin Johansson * Elib AB * 1,05 s *
* Magnus Sörin * Avanade * 1,28 s *
* Sergey Klimkevich * Memnon Networks * 1,44 s *
* Ola Hallengren * Saxo Bank * 2,15 s *
***************************************************************

Obs att de avgörande körningarna kommer att göras på en större mängd indata och att resultatlistan då kan ändras radikalt!

MVH
Johan

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
27 Nov 2010 08:08 AM
Observera att nu gör vi inga fler provkörningar innan den stora finalkörningen.

Ni är välkomna att posta nya tävlingsbidrag fram till den 30:e november kl 22:00. Bidrag som kommer in senare diskvalificeras.

Lycka till!
martin.johansson
New Member
New Member
Posts: 13


--
30 Nov 2010 12:59 AM

Har rensat lite onödig kod och kolumner.
Ger i snitt en 30 ms på laptopen och testdatabasen men lösningen blir renare såhär.

/*********************

* Martin Johansson *

* Elib AB *

* v 1.1 *

*********************/

use SQLSM2010

-- setup

-- unikt index ver VehicleName och StartTime

create unique nonclustered index VehicleName_StartTime_uix

on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue) with (fillfactor = 100)

-- indexering av VehicleName

create nonclustered index VehicleName_ix

on dbo.MeterReadings(VehicleName asc) with (fillfactor = 100)

go

-- rensa tabellen ConsumptionSummary

truncate table dbo.ConsumptionSummary

go

USE [SQLSM2010]

GO

-- För att falla för grupptrycket

set nocount on

go

-- Main run

-- Cursor för att hantera antalet rader (Alternativt skulle man kunna göra någon slags set based iteration men det skulle inte sticka tillräckligt i folks ögon :)

declare crs_LiftCount cursor

for

select (MAX(a.RowID)-1)*0.2, a.VehicleName

from (select ROW_NUMBER() over (partition by vehiclename order by vehiclename, starttime) as RowID, VehicleName, StartTime, MeterReadings.MeterValue from dbo.MeterReadings) as a

group by vehiclename

declare @lccC int -- Håller antalet rader

declare @lccN varchar(50) -- Håller VehicleName

open crs_LiftCount

fetch next from crs_LiftCount into @lccC, @LccN

WHILE @@FETCH_STATUS = 0

BEGIN

-- CTE med aggregerade funktioner för läsbarhet

WITH MeterReadingsRowID(rowID, vehicleName,startTime, meterValue) AS

(

select ROW_NUMBER() over (partition by vehiclename order by vehiclename, starttime) as rownum,

VehicleName,

StartTime,

MeterValue

from dbo.MeterReadings

Where vehiclename=@lccN

), ConsumptionTop20(VehicleName, MilliSeconds, Consumption, ConsumptionPerMinute) as

(

-- Rensat lite onödiga kolumner. Nästa steg är att ta bort den dubbla DATEDIFF som förekommer.

select top(@lccC) a.VehicleName, DATEDIFF(MILLISECOND, b.starttime, a.starttime), a.MeterValue - b.MeterValue, convert(float,b.MeterValue - a.MeterValue) / convert(float, DATEDIFF(millisecond, a.StartTime, b.StartTime)) * 60000 AS cons_per_min

from MeterReadingsRowID a inner join MeterReadingsRowID b on a.rowID= b.rowID +1

order by cons_per_min desc, a.starttime desc

)

Insert Into ConsumptionSummary

select VehicleName, 60000 * Convert(float,SUM(consumption))/convert(float, SUM(milliseconds)) as WeightedConsumptionPerMinute

from ConsumptionTop20

group by vehiclename;

fetch next from crs_LiftCount into @lccC, @LccN

END

close crs_LiftCount

deallocate crs_LiftCount

GO

-- cleanup

-- Ta bort unikt index ver Vehiclename och StartTime

drop index MeterReadings.VehicleName_StartTime_uix

-- Ta bort index ver VehicleName

drop index MeterReadings.VehicleName_ix

set nocount off

go

Sergey
New Member
New Member
Posts: 16


--
30 Nov 2010 02:49 PM


/***************************************
Author: Sergey Klimkevich
Company: Memnon Networks AB
Version: 6
****************************************/

-- SETUP
CREATE UNIQUE NONCLUSTERED INDEX [ix_name_time] ON [dbo].[MeterReadings]
(
[VehicleName] ASC,
[StartTime] asc
)
INCLUDE ( [MeterValue]) WITH (FILLFACTOR = 100)
GO
CREATE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
(
[VehicleName] ASC
) WITH (FILLFACTOR = 100)
GO

CREATE FUNCTION dbo.itvf(@vn varchar(50),@c int)
RETURNS float
AS Begin
declare @res float
;with [OrderedStarts] as (
SELECT
row_number() over(order by StartTime) rn
,[StartTime]
,[MeterValue]
FROM [dbo].[MeterReadings] with(nolock)
where VehicleName = @vn
)
select @res=convert(float,sum(m))/sum(ms) * 60000
from (
select row_number() over(order by
convert(float,t2.[MeterValue] - t1.[MeterValue])/datediff(ms,t1.[StartTime],t2.[StartTime]) desc
,t2.[StartTime] desc
) rn
,t2.[MeterValue] - t1.[MeterValue] m
,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
from [OrderedStarts] t1
join [OrderedStarts] t2
on t1.rn = t2.rn -1
) [stats]
where rn <= @c

return @res
end
GO
truncate table dbo.[ConsumptionSummary]
GO
set nocount on
GO

-- MAIN RUN

INSERT ConsumptionSummary ([VehicleName],[Top20PctAveragePerMinute])
SELECT
[VehicleName]
,dbo.itvf ([VehicleName], (count(*)+3)/5) AS Top20PctAveragePerMinute
FROM [dbo].[MeterReadings] with(nolock)
group by [VehicleName]

-- CLEAN UP

DROP FUNCTION dbo.itvf
GO
drop index ix_name_time ON [dbo].[MeterReadings]
drop index ix_name ON [dbo].[MeterReadings]
GO
truncate table [ConsumptionSummary]
GO
set nocount off
GO

SwePeso
New Member
New Member
Posts: 67


--
30 Nov 2010 08:38 PM
Version 1

/* Setup
CREATE NONCLUSTERED INDEX UX_Peso ON dbo.MeterReadings (VehicleName, StartTime) INCLUDE (MeterValue)
UPDATE STATISTICS dbo.MeterReadings WITH FULLSCAN
DBCC UPDATEUSAGE(0, 'dbo.MeterReadings') WITH COUNT_ROWS
*/

/* Main
*/
TRUNCATE TABLE dbo.ConsumptionSummary

;WITH cteSource(VehicleName, theTime, theMeter, RecID)
AS (
SELECT VehicleName,
StartTime AS theTime,
MeterValue AS theMeter,
ROW_NUMBER() OVER (PARTITION BY VehicleName ORDER BY StartTime) AS RecID
FROM dbo.MeterReadings
), cteSpan(VehicleName, TimeSpan, MeterSpan, theTime)
AS (
SELECT prev.VehicleName,
DATEDIFF(MILLISECOND, prev.theTime, curr.theTime) AS TimeSpan,
curr.theMeter - prev.theMeter AS MeterSpan,
curr.theTime
FROM cteSource AS prev
INNER JOIN cteSource AS curr ON curr.VehicleName = prev.VehicleName
AND curr.RecID = prev.RecID + 1
), cteTop(VehicleName, TimeSpan, MeterSpan, Percentile)
AS (
SELECT VehicleName,
TimeSpan,
MeterSpan,
NTILE(5) OVER (PARTITION BY VehicleName ORDER BY 1000.E * MeterSpan / TimeSpan DESC, theTime DESC) AS Percentile
FROM cteSpan
)
INSERT dbo.ConsumptionSummary
(
VehicleName,
Top20PctAveragePerMinute
)
SELECT VehicleName,
60000.E * SUM(MeterSpan) / SUM(TimeSpan) AS Top20PctAveragePerMinute
FROM cteTop
WHERE Percentile = 1
GROUP BY VehicleName

/* Teardown
DROP INDEX UX_Peso ON dbo.MeterReadings
*/
 

SwePeso
New Member
New Member
Posts: 67


--
30 Nov 2010 08:43 PM
Version 2

/* Setup
CREATE NONCLUSTERED INDEX UX_Peso ON dbo.MeterReadings (VehicleName, StartTime) INCLUDE (MeterValue)
UPDATE STATISTICS dbo.MeterReadings WITH FULLSCAN
DBCC UPDATEUSAGE(0, 'dbo.MeterReadings') WITH COUNT_ROWS
*/

/* Main
*/
TRUNCATE TABLE dbo.ConsumptionSummary

;WITH cteSource(VehicleName, TimeSpan, MeterSpan, Percentile)
AS (
SELECT prev.VehicleName,
DATEDIFF(MILLISECOND, prev.StartTime, curr.StartTime) AS TimeSpan,
curr.MeterValue - prev.MeterValue AS MeterSpan,
NTILE(5) OVER (PARTITION BY prev.VehicleName ORDER BY 1.E * (curr.MeterValue - prev.MeterValue) / DATEDIFF(MILLISECOND, prev.StartTime, curr.StartTime) DESC, prev.StartTime DESC) AS Percentile
FROM dbo.MeterReadings AS prev
CROSS APPLY (
SELECT TOP(1) mr.StartTime,
mr.MeterValue
FROM dbo.MeterReadings AS mr
WHERE mr.VehicleName = prev.VehicleName
AND mr.StartTime > prev.StartTime
ORDER BY mr.StartTime
) AS curr
)
INSERT dbo.ConsumptionSummary
(
VehicleName,
Top20PctAveragePerMinute
)
SELECT VehicleName,
60000.E * SUM(MeterSpan) / SUM(TimeSpan) AS Top20PctAveragePerMinute
FROM cteSource
WHERE Percentile = 1
GROUP BY VehicleName

/* Teardown
DROP INDEX UX_Peso ON dbo.MeterReadings
*/
Henrik Nilsson
New Member
New Member
Posts: 9


--
30 Nov 2010 09:42 PM
-------------------------------------
-- Author: Henrik Nilsson
-- Company: Brummer & Partners
-- Edition: 1
-- Version: 1
-------------------------------------

-- Setup
SET NOCOUNT ON
CREATE UNIQUE NONCLUSTERED INDEX IX ON dbo.MeterReadings(VehicleName ASC, MeterReadingID ASC) INCLUDE (StartTime, MeterValue) WITH (FILLFACTOR = 100) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX2 ON dbo.MeterReadings (VehicleName ASC) WITH (FILLFACTOR = 100) ON [PRIMARY]
TRUNCATE TABLE dbo.ConsumptionSummary
GO

-- Main Run
INSERT INTO dbo.ConsumptionSummary(VehicleName, Top20PctAveragePerMinute)
SELECT m.VehicleName
,(
SELECT (60000.0 * SUM(d.Consumtion) / SUM(d.TimeInMilliseconds))
FROM (
SELECT TOP 20 PERCENT
m1.VehicleName
,m2.MeterValue - m1.MeterValue AS Consumtion
,DATEDIFF(millisecond, m1.StartTime, m2.StartTime) AS TimeInMilliseconds
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY mr.MeterReadingID) AS row
,mr.VehicleName
,mr.StartTime
,mr.MeterValue
FROM dbo.MeterReadings AS mr
WHERE mr.VehicleName = m.VehicleName
) AS m1
INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY mr.MeterReadingID) AS row
,mr.VehicleName
,mr.StartTime
,mr.MeterValue
FROM dbo.MeterReadings AS mr
WHERE mr.VehicleName = m.VehicleName
) AS m2 ON m2.row = m1.row - 1
WHERE 1 = 1
ORDER BY (m2.MeterValue - m1.MeterValue) /(1.0 * datediff(ms, m1.StartTime, m2.StartTime)) DESC, m1.StartTime DESC
) AS d
WHERE 1 = 1
GROUP BY d.VehicleName
) AS Top20PctAveragePerMinute
FROM dbo.MeterReadings AS m
GROUP BY m.VehicleName
GO

-- Cleanup
DROP INDEX IX ON dbo.MeterReadings
DROP INDEX IX2 ON dbo.MeterReadings
SET NOCOUNT OFF
GO


-------------------------------------
-- Author: Henrik Nilsson
-- Company: Brummer & Partners
-- Edition: 2
-- Version: 1
-------------------------------------

-- Setup
SET NOCOUNT ON
CREATE UNIQUE NONCLUSTERED INDEX [IX] ON [dbo].[MeterReadings]([VehicleName] ASC, [MeterReadingID] ASC) INCLUDE ([StartTime],[MeterValue]) WITH (FILLFACTOR = 100) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX2] ON [dbo].[MeterReadings] ([VehicleName] ASC) WITH (FILLFACTOR = 100) ON [PRIMARY]
TRUNCATE TABLE dbo.ConsumptionSummary
GO

-- Main Run
INSERT INTO dbo.ConsumptionSummary (VehicleName, Top20PctAveragePerMinute)
SELECT v.VehicleName
,SUM(mr3.Consumtion) * 60000.0 / SUM(mr3.TimeInMilliseconds) AS [Top20PctAveragePerMinute]
FROM (
-- get all unique Vehicles
SELECT DISTINCT
mr.VehicleName
FROM dbo.MeterReadings AS mr
) AS v
CROSS APPLY (
-- for each unique Vehicle get the rows with the highest consumtion per timeunit
SELECT TOP 20 PERCENT
mr2.MeterValue - mr.MeterValue AS [Consumtion]
,DATEDIFF(MILLISECOND, mr.StartTime, mr2.StartTime) AS [TimeInMilliseconds]
FROM dbo.MeterReadings AS mr
CROSS APPLY (
SELECT TOP 1
m.MeterValue
,m.StartTime
FROM dbo.MeterReadings AS m
WHERE 1 = 1
AND m.VehicleName = mr.VehicleName
AND m.MeterReadingID > mr.MeterReadingID
) AS mr2
WHERE 1 = 1
AND mr.VehicleName = v.VehicleName
ORDER BY (mr2.MeterValue - mr.MeterValue)/(1.0 * DATEDIFF(MILLISECOND, mr.StartTime, mr2.StartTime)) DESC
,mr.MeterReadingID DESC
) AS mr3
GROUP BY v.VehicleName
GO

-- Cleanup
DROP INDEX IX ON dbo.MeterReadings
DROP INDEX IX2 ON dbo.MeterReadings
SET NOCOUNT OFF
GO


Malin Fröier
New Member
New Member
Posts: 3


--
30 Nov 2010 09:47 PM
/*******************
Malin Davidsson, Affecto
*******************/

/*
Setup
*/

set nocount on
go

truncate table dbo.ConsumptionSummary
go
create unique index IX_MeterReadings on dbo.MeterReadings(vehiclename, starttime)
create index IX_MeterReadings2 on dbo.MeterReadings( starttime, vehiclename)

go

/*
Huvudkod
*/

create view v_MeterReadings as
select row_number() over( partition by vehiclename order by starttime) rnbr, vehiclename, starttime, metervalue
from dbo.MeterReadings

go
create view v_topNbr as
select vehiclename, CEILING(0.2*(count(*)-1)) nbr
from dbo.MeterReadings
group by vehiclename
go

create table #tmpConsumption(
rnbr int,
vehiclename varchar(50),
timediff int,
consumption int)

insert into #tmpConsumption
select row_number() over(partition by t1.vehiclename order by (t2.metervalue-t1.metervalue)/cast(datediff(ms,t1.starttime, t2.starttime) as float) desc, t1.starttime desc) rnbr, t1.vehiclename, datediff(ms,t1.starttime, t2.starttime) timediff, t2.metervalue-t1.metervalue consumption
from dbo.v_MeterReadings t1
join dbo.v_MeterReadings t2
on t1.vehiclename=t2.vehiclename and t1.rnbr+1=t2.rnbr

insert into ConsumptionSummary
select t1.vehiclename, 60000*(sum(t1.consumption)/cast(sum(t1.timediff) as float))
from #tmpConsumption t1
join dbo.v_topNbr t2
on t1.vehiclename=t2.vehiclename and t1.rnbr<=t2.nbr
group by t1.vehiclename

/*
Uppstädning
*/
drop view dbo.v_MeterReadings
drop view dbo.v_topNbr
drop table #tmpConsumption
drop index dbo.MeterReadings.IX_MeterReadings
drop index dbo.MeterReadings.IX_MeterReadings2
go


JAhlen
Veteran Member
Veteran Member
Posts: 144


--
30 Nov 2010 10:16 PM
Tack för alla tävlingsbidrag!

Vi kommer nu att göra en noggrann provkörning och återkommer när vinnarna är utsedda!

MVH
Johan
SwePeso
New Member
New Member
Posts: 67


--
01 Dec 2010 07:27 AM
Har du en uppfattning om det blir klart den här veckan?
Steinar
New Member
New Member
Posts: 13


--
01 Dec 2010 04:01 PM
Vinnarna kommer antagligen att annonseras i början på nästa vecka
You are not authorized to post a reply.
Page 3 of 4 << < 1234 > >>