Bericht, um die Besuche neuer und alter Benutzer täglich, monatlich oder jährlich aufzulisten - mysql, sql, sql-server-2008, ms-access

Ich habe 2 Tabellen mit folgender Tabellenstruktur -

Benutzer (Benutzeridentifikation int, Name varchar (50), Datum erstellt datetime) und

Benutzer-Anmeldung (Ich würde int, Benutzeridentifikation int, DateLoggedIn Terminzeit)

Ich muss einen Bericht von Benutzern erstellen, die sich jeden Tag / Monat / Jahr im folgenden Format bei der Anwendung anmelden:

Datum, NewUsers, OldUsers, TotalUsers

Datum - Für den Bericht ausgewähltes Datum

Neuer Benutzer - Wenn sich der Benutzer zum ersten Mal in der Anwendung anmeldet, wird er als neuer Benutzer als neuer Benutzer behandelt.

Alter Benutzer - Nachfolgende Benutzeranmeldungen nach dem ersten Besuch.

Für z.B.Wenn sich Benutzer A für einen Monat auf dem 15. Platz einloggt, wird er als neuer Benutzer am 15., aber als alter Benutzer für nachfolgende Besuche auf der Website behandelt und muss als alter Benutzer im Bericht gemeldet werden. Wenn ein Benutzer B sich am 30.4.2012 einmal angemeldet hat, wird er als alter Benutzer behandelt.

Können Sie mir bitte eine Frage stellen, um den Bericht auszugeben?

Meine Daten sind wie folgt -

USER-Tabelle

| BenutzerID | Name | Datum erstellt|
| -------- | -------------- | ------------- |
| 1 | Rajveer | 2012-01-01 |
| 2 | Manoj Tyagi | 2011-08-10 |
| 3 | Raghu Pandey | 2009-05-13 |
| 4 | Bhajan Lal | 2012-05-01 |
| 5 | Satbir Singh | 2011-08-03 |

USERLOGIN-Tabelle

| ID | BenutzerID | DateLoggedIn|
| ---- | -------- | -------------- |
| 1 | 1 | 2012-04-27 |
| 2 | 1 | 2012-05-25 |
| 3 | 1 | 2012-05-28 |
| 4 | 2 | 2012-03-21 |
| 5 | 2 | 2012-05-30 |
| 6 | 3 | 2012-05-20 |
| 7 | 4 | 2012-05-29 |
| 8 | 5 | 2012-04-22 |
| 9 | 3 | 2012-05-15 |

Ich brauche ganze Daten für einen Monat (so unterschiedlich ist nicht erforderlich) .... idealerweise basierend auf den Daten, die ich zur Verfügung gestellt habe, sollte ich Folgendes bekommen

| Monat | Neue Benutzer | Alte Benutzer | Gesamtbenutzer|
| ------- | ---------- | ---------- | ------------ |
| 3 | 1 | 0 | 1 |
| 4 | 2 | 0 | 2 |
| 5 | 2 | 4 | 6 |

Da Benutzer-ID 3 im Mai zweimal angemeldet war, sollte sein erster Besuch als neu und der zweite als alt behandelt werden.

TIA,

Paritosh Arya.

Antworten:

0 für die Antwort № 1

Sie können am Anmeldedatum einen Outer-Join zwischen der Login-Tabelle und der Benutzertabelle durchführen und dann die verschiedenen Benutzer-IDs in jeder Tabelle zählen:

SELECT   DATE(l.DateLoggedIn)                                AS Date,
COUNT(DISTINCT u.UserID)                            AS NewUsers,
COUNT(DISTINCT l.UserId) - COUNT(DISTINCT u.UserID) AS OldUsers,
COUNT(DISTINCT l.UserId)                            AS TotalUsers
FROM     UserLogin                                           AS l
LEFT JOIN Users                                            AS u
ON l.UserId = u.UserID
AND DATE(l.DateLoggedIn) = DATE(u.DateCreated)
GROUP BY Date

0 für die Antwort № 2

Ich denke, das zeigt die Logik, aber möglicherweise nicht die höchste Leistung.

    ;With NewUser as (
Select ul.UserID
, Min(ul.DateLoggedIn) as NewDate
from dbo.USERLOGIN as ul
group by UserID)

Select
Month(l.DateLoggedIn) as ThisMonth
, (Select Count(*) from dbo.USERLOGIN as ul
inner join NewUser as nu
on ul.UserID = nu.UserID
and ul.DateLoggedIn = nu.NewDate
where Month(ul.DateLoggedIn) = Month(l.DateLoggedIn)
) as NewUsers
, (Select Count(*) from dbo.USERLOGIN as ul
where Month(ul.DateLoggedIn) = Month(l.DateLoggedIn)
AND not exists (Select * from NewUser as nu
where ul.UserID = nu.UserID
and ul.DateLoggedIn = nu.NewDate)
) as OldUsers
, Count(*) as TotalUsers
from dbo.USERLOGIN as l
group by Month(l.DateLoggedIn)

-- a little testing code
CREATE TABLE [dbo].[USERLOGIN](
[ID] [int] NOT NULL,
[USERID] [int] NOT NULL,
[DateLoggedIn] [date] NOT NULL
)
;
insert INTO dbo.USERLOGIN

select 1, 1, "2012-04-27"
union all
Select 2, 1, "2012-05-25"
union all
Select  3 , 1,"2012-05-28"
union all
Select 4 ,  2,"2012-03-21"
union all
Select 5 , 2,"2012-05-30"
union all
Select 6 , 3 ,"2012-05-20"
union all
Select 7 , 4 ,"2012-05-29"
union all
Select 8 , 5,"2012-04-22"
union all
Select  9 , 3,"2012-05-15"

Verwandte Fragen