दैनिक, मासिक या वार्षिक - नए और पुराने उपयोगकर्ता की यात्राओं को सूचीबद्ध करने की रिपोर्ट करें - mysql, sql, sql-server-2008, ms-access

मेरे पास निम्नलिखित तालिका संरचना के साथ 2 टेबल हैं -

उपयोगकर्ता (यूज़र आईडी int, नाम varchar (50), निर्माण की तिथि डेटाटाइम) और

उपयोगकर्ता लॉगिन (ईद पूर्णांक, यूज़र आईडी पूर्णांक, DateLoggedIn दिनांक समय)

मुझे उन उपयोगकर्ताओं की एक रिपोर्ट तैयार करने की आवश्यकता है जो निम्नलिखित प्रारूप में हर दिन / माह / वर्ष में आवेदन में लॉग इन कर रहे हैं -

तिथि, न्यूउसर, ओल्ड यूजर, कुल यूज़र

तारीख - रिपोर्ट के लिए चुना गया दिनांक

नया उपयोगकर्ता - यदि उपयोगकर्ता एप्लिकेशन में पहली बार लॉग इन करता है, तो उसे पुराने उपयोगकर्ता के रूप में अन्यथा नया माना जाएगा।

OldUser - पहली बार विज़िट के बाद उपयोगकर्ता लॉग इन करें।

उदाहरण के लिए, यदि उपयोगकर्ता एक महीने के लिए 15 वें स्थान पर लॉग इन करता है, तो उसे 15 वें स्थान पर नए उपयोगकर्ता के रूप में माना जाएगा, लेकिन वेबसाइट पर आने वाली विज़िट के लिए पुराने उपयोगकर्ता के रूप में और रिपोर्ट पर पुराने उपयोगकर्ता के रूप में रिपोर्ट करने की आवश्यकता होगी। इसी तरह यदि कोई उपयोगकर्ता बी 30/4/2012 को एक बार लॉग इन कर चुका है, तो उसके बाद उसे पुराने उपयोगकर्ता के रूप में माना जाएगा।

क्या आप कृपया मुझे रिपोर्ट आउटपुट करने के लिए एक प्रश्न दे सकते हैं?

मेरा डेटा इस प्रकार है -

यूजर टेबल

| उपयोगकर्ता आईडी | नाम | निर्माण की तिथि|
| -------- | -------------- | ------------- |
| 1 | राजवीर | 2012-01-01 |
| 2 | मनोज त्यागी | 2011-08-10 |
| 3 | रघु पांडे | 2009-05-13 |
| 4 | भजन लाल | 2012-05-01 |
| 5 | सतबीर सिंह | 2011-08-03 |

USERLOGIN तालिका

| आईडी | उपयोगकर्ता आईडी | 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 |

मुझे एक महीने के लिए पूरे डेटा की आवश्यकता है (इसलिए विशिष्ट की आवश्यकता नहीं है) .... आदर्श रूप से प्रदान किए गए डेटा के आधार पर, मुझे निम्न प्राप्त करना चाहिए

| महीना | न्यू यूज़र | OldUsers | TotalUsers|
| ------- | ---------- | ---------- | ------------ |
| 3 | 1 | 0 | 1 |
| 4 | 2 | 0 | 2 |
| 5 | 2 | 4 | 6 |

चूंकि उपयोगकर्ता आईडी 3 मई में दो बार लॉग इन हो गया है, इसलिए उसकी पहली यात्रा को नए और दूसरे के रूप में पुराना माना जाना चाहिए।

TIA,

परितोष आर्य।

उत्तर:

जवाब के लिए 0 № 1

आप लॉगिन तालिका और उपयोगकर्ता तालिका के बीच जुड़ने की तारीख के बीच बाहरी जुड़ सकते हैं और फिर प्रत्येक तालिका में अलग उपयोगकर्ता आईडी की गणना कर सकते हैं:

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 № 2

मुझे लगता है कि यह तर्क दिखाता है लेकिन उच्चतम प्रदर्शन नहीं हो सकता है।

    ;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"

संबंधित सवाल
सबसे लोकप्रिय