[SQL] 'Aantal dagen actief' berekenen

Pagina: 1
Acties:
  • 965 views sinds 30-01-2008
  • Reageer

Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
SQL'ers, ik zit met een vraagstuk:

In onze webshop wordt op basis van verkopen in het verleden een bepaald inkoopadvies gegenereerd. Daarbij hoort een bepaalde formule.
Nu moet de formule zo aangepast worden, dat het een variabele "aantal dagen actief" heeft. Dit is het aantal dagen dat het product op actief heeft gestaan (een product kan actief of inactief zijn).
Dit betekent dus dat, in de database, bijgehouden moet worden wanneer een product actief of inactief is geworden: elke keer als dit veranderd wordt er een regel weggeschreven met de datum en de status van het product.

productX actief 01.01.2007
productX inactief 01.02.2007
productX actief 15.02.2007
productX inactief 19.03.2007
etc.

Is dit een goede manier en: hoe kan ik in een eenvoudige SNELLE! query het aantal dagen opvragen dat het product actief is geweest in de laatste X maanden.

een tweede manier is dat ik een extra kolom opneem, dat bijhoud hoeveel dagen het product actief is geweest sinds de laatste activatie. elke keer als het product van status veranderd, bekijk ik het laatste record, bereken het aantal dagen en voeg dit toe aan het record:

productX actief 01.01.2007 0
productX inactief 01.02.2007 31
productX actief 15.02.2007 0
productX inactief 19.03.2007 32

de query is nu eenvoudiger: sum(dagen) / where datum between X and Y / group by product

echter, volgens de normalisatie regels is deze kolom een 'proces gegeven' (omdat het ook af te leiden is d.m.v. de rijen en data).

Wie heeft hier een goed (ander) idee voor, of geeft me een goede query voor de eerste oplossing ?

Acties:
  • 0 Henk 'm!

  • P.O. Box
  • Registratie: Augustus 2005
  • Niet online
gebruik je MySQL of SQL Server of een andere SQL versie?

ik zat zelf te denken aan een subselect waarin je bij een activatie de bijbehorende deactivatie zoekt door het eerste record te pakken met een datum/tijd groter dan de datum/tijd van de activatie....

Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
SQL Server 2005.

het belangrijkste vraagstuk is: geef mijn het aantal dagen dat productX actief is geweest in de laatste X maanden. Hoe het verder opgelost wordt, maakt in principe niet uit. als het maar snel werkt. (omdat van een hele rij producten tegelijk de statistieken / adviezen berekend moeten worden, en over een periode van meerdere maanden)

Acties:
  • 0 Henk 'm!

  • Skinny
  • Registratie: Januari 2000
  • Laatst online: 12:45

Skinny

DIRECT!

Met het volgende tabelletje en er vanuit gaande dat de status een numerieke waarde is (0=inactief, 1=actief) werkt dit best aardig ;)

code:
1
2
3
4
5
CREATE TABLE [dbo].[Status](
    [ProductID] [int] NULL,
    [DateTime] [datetime] NULL CONSTRAINT [DF_Status_DateTime]  DEFAULT (getdate()),
    [Status] [int] NULL
) ON [PRIMARY]


code:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    S1.ProductID,
    S1.DateTime AS StartDate,
    MIN(ISNULL(S2.DateTime,GETDATE())) AS EndDate,
    S1.Status,
    DATEDIFF(dd,S1.DateTime,MIN(ISNULL(S2.DateTime,GETDATE()))) AS DayCount
        
    FROM Status S1
        LEFT JOIN Status S2 ON (S1.ProductID = S2.ProductID
                AND S1.DateTime < S2.DateTime AND S1.Status <> S2.Status)

GROUP BY S1.ProductID, S1.DateTime,S1.Status
ORDER BY S1.ProductID, S1.DateTime


code:
1
2
3
4
5
6
7
8
9
10
11
12
ProductID   StartDate               EndDate                 Status      DayCount
----------- ----------------------- ----------------------- ----------- -----------
1           2006-05-23 10:41:25.000 2006-12-23 10:41:31.000 1           214
1           2006-12-23 10:41:31.000 2007-01-23 10:41:32.000 0           31
1           2007-01-23 10:41:31.000 2007-01-23 10:41:32.000 0           0
1           2007-01-23 10:41:32.000 2007-05-23 10:51:52.783 1           120
2           2006-11-23 10:41:27.000 2007-05-23 10:41:33.257 1           181
2           2007-05-23 10:41:33.257 2007-05-23 10:51:52.783 0           0
3           2006-09-23 10:41:28.000 2007-05-23 10:51:52.783 1           242
3           2007-03-23 10:41:34.000 2007-05-23 10:51:52.783 1           61
4           2007-05-23 10:41:29.290 2007-05-23 10:51:52.783 1           0
5           2007-05-23 10:41:30.290 2007-05-23 10:51:52.783 1           0

SIZE does matter.
"You're go at throttle up!"


Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
dus om het totaal ACTIEVE dagen te krijgen doe ik:

code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT ProductID, SUM(DayCount) AS ActieveDagen
FROM (
    SELECT
        S1.ProductID,
        S1.DateTime AS StartDate,
        MIN(ISNULL(S2.DateTime,GETDATE())) AS EndDate,
        S1.Status,
        DATEDIFF(dd,S1.DateTime,MIN(ISNULL(S2.DateTime,GETDATE()))) AS DayCount
            
        FROM Status S1
            LEFT JOIN Status S2 ON (S1.ProductID = S2.ProductID
                    AND S1.DateTime < S2.DateTime AND S1.Status <> S2.Status)
    
    GROUP BY S1.ProductID, S1.DateTime,S1.Status
    ORDER BY S1.ProductID, S1.DateTime
) S3
WHERE S3.actief = 1
GROUP BY ProductID

Acties:
  • 0 Henk 'm!

  • JJvG
  • Registratie: Juli 2003
  • Laatst online: 31-05 13:43
Volgens mij is je datamodel nog niet helemaal uit genormaliseerd. In principe moet je geen berekende waarden in je database opslaan, omdat je die kan afleiden uit je andere data.

Mijn idee is dat je het actieve product als volgt bijhoudt:
ProductID, DateActiveStart, DateActiveEnd

Wanneer het actief is kun je de datum DateActiveEnd Null laten (of groter dan getdate())
Query voor bepalen actieve producten:
SELECT ProductID
FROM table
WHERE DateActiveStart < getdate()
AND (DateActiveEnd IS NULL OR DateActiveEnd > getdate())

Query voor bepalen actieve tijd (pseudo-sql):
SELECT ProductID, datediff("d", DateActiveStart, DateActiveEnd )
FROM table
WHERE DateActiveStart BETWEEN 2007-01-01 AND 2007-04-01

Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
@JJvG

zoals ik al aangaf is mijn tweede methode dus niet goed volgens de normalisatie regels, en zoek ik de juiste query bij de eerste oplossing die ik gaf.

jouw methode werkt trouwens sowieso niet. Want een product kan in een bepaalde tijd meerdere keren wisselen van actief naar inactief en weer actief, etc. (zie de voorbeeld records).

een simpele datediff() werkt dus niet.

Ik denk (ik kan vanavond pas gaan testen), dat de query van Skinny de oplossing is.

Daarna zal ik even naar de performance kijken, mocht die niet acceptabel zijn. dan ga ik alsnog voor de oplossing met een 'voorberekende' kolom (tegen normalisatie in dus).

Acties:
  • 0 Henk 'm!

  • KoW
  • Registratie: Juli 2001
  • Laatst online: 17-08-2022

KoW

Parse parsed te veel

- overbodig ivm voorgaande reactie

[ Voor 85% gewijzigd door KoW op 23-05-2007 11:19 ]

Pagina: 1