Home » SQL & PL/SQL » SQL & PL/SQL » count per group (web intelligence)
count per group [message #680676] |
Sun, 31 May 2020 10:37 |
|
jtylerrand
Messages: 3 Registered: May 2020
|
Junior Member |
|
|
I am working in Web Intelligence Business Objects and it uses Oracle PL/SQL. I have no ability to create temp tables, so I need to have a single SQL statement that will return the desired results.
The data has the following format:
Sector, Customer_ID, Fiscal Year, Datetime
Sample data:
SDC, 1234, 2030, 3/21/2020
SDC, 456, 2020, 3/23/2020
ELC, 1234, 2020, 3/24/2020
SDC, 1234, 2020, 3/28/2020
I need to identify the count of customer_IDs per sector where that returned anywhere after their initial visit to each sector.
So in the above example, customer_ID 1234 would be credited to Sector SDC since he had a transaction after his initial. Customer_ID 456 would not be counted for ELC since he did not have a subsequent transaction after their initial.
Customer_ID 1234 would also be credited to Sector ELC because he returned after his visit to ELC. It doesn't matter where the follow-up visit is made, only that a follow-up visit occurs within the time frame.
The important part is that every sector where the customer_ID shows up gets credit for them only if they return anywhere afterwards.
I imagine it would look something like this:
Select R.Sector, R.customer_ID, count(R.Return)
From
(
(Select fiscal year, customer_ID, count(ROWNUM) as Returns
From Table
GROUP BY customer_ID, fiscal year
Having count(rownum)>1) T,
(Select min(row num) as min_app, customer_ID, sector
From Table
Group by sector, customer_ID) T1
Where T.customer_ID=T1.customer_ID
) R
Group by R.Sector, R.customer_ID
Where T.row num> T1.min_app
I can identify the customers who return more than once to the same sector, but I can't seem to also get the customers that return to a different sectors, but give the first sector the credit for that customer.
Hopefully my explanation is thorough enough for the geniouses of this forum to help. Feel free to ask any additional questions. Thanks for your time.
Tyler
|
|
|
|
Re: count per group [message #680678 is a reply to message #680676] |
Sun, 31 May 2020 13:37 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
WITH SAMPLE(SECTOR, CUSTOMER_ID, FISCALYEAR, DATETIME)
AS (
SELECT 'SDC',1234,2020,DATE '2020-3-21' FROM DUAL UNION ALL
SELECT 'SDC',456,2020,DATE '2020-3-23' FROM DUAL UNION ALL
SELECT 'ELC',1234,2020,DATE '2020-3-24' FROM DUAL UNION ALL
SELECT 'SDC',1234,2020,DATE '2020-3-28' FROM DUAL
),
T AS (
SELECT SECTOR,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATETIME) > 1 THEN NULL
WHEN COUNT(*) OVER(PARTITION BY CUSTOMER_ID) = 1 THEN NULL
ELSE CUSTOMER_ID
END CUSTOMER_ID
FROM SAMPLE
)
SELECT SECTOR,
COUNT(CUSTOMER_ID) CNT
FROM T
GROUP BY SECTOR
ORDER BY SECTOR
/
SECTOR CNT
------ ----------
ELC 0
SDC 1
SQL>
SY.
|
|
|
Re: count per group [message #680680 is a reply to message #680678] |
Sun, 31 May 2020 18:47 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Ah, I missed "Customer_ID 1234 would also be credited to Sector ELC because he returned after his visit to ELC":
WITH SAMPLE(SECTOR, CUSTOMER_ID, FISCALYEAR, DATETIME)
AS (
SELECT 'SDC',1234,2020,DATE '2020-3-21' FROM DUAL UNION ALL
SELECT 'SDC',456,2020,DATE '2020-3-23' FROM DUAL UNION ALL
SELECT 'ELC',1234,2020,DATE '2020-3-24' FROM DUAL UNION ALL
SELECT 'SDC',1234,2020,DATE '2020-3-28' FROM DUAL
),
T AS (
SELECT SECTOR,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATETIME) = COUNT(*) OVER(PARTITION BY CUSTOMER_ID) THEN NULL
ELSE CUSTOMER_ID
END CUSTOMER_ID
FROM SAMPLE
)
SELECT SECTOR,
COUNT(CUSTOMER_ID) CNT
FROM T
GROUP BY SECTOR
ORDER BY SECTOR
/
SECTOR CNT
------ ----------
ELC 1
SDC 1
SQL>
SY.
|
|
|
Re: count per group [message #680681 is a reply to message #680680] |
Sun, 31 May 2020 18:51 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Also, it isn't clear if you want count of customers or count of distinct customers. If later, use:
WITH SAMPLE(SECTOR, CUSTOMER_ID, FISCALYEAR, DATETIME)
AS (
SELECT 'SDC',1234,2020,DATE '2020-3-21' FROM DUAL UNION ALL
SELECT 'SDC',456,2020,DATE '2020-3-23' FROM DUAL UNION ALL
SELECT 'ELC',1234,2020,DATE '2020-3-24' FROM DUAL UNION ALL
SELECT 'SDC',1234,2020,DATE '2020-3-28' FROM DUAL
),
T AS (
SELECT SECTOR,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATETIME) = COUNT(*) OVER(PARTITION BY CUSTOMER_ID) THEN NULL
ELSE CUSTOMER_ID
END CUSTOMER_ID
FROM SAMPLE
)
SELECT SECTOR,
COUNT(DISTINCT CUSTOMER_ID) CNT
FROM T
GROUP BY SECTOR
ORDER BY SECTOR
/
SY.
|
|
|
Re: count per group [message #680692 is a reply to message #680681] |
Mon, 01 June 2020 09:54 |
|
jtylerrand
Messages: 3 Registered: May 2020
|
Junior Member |
|
|
Solomon,
First off I wanted to thank you for your response, I greatly appreciate the help.
I need the distinct count of Customers per Sector with at least one return after visiting that Sector.
A Sector gets credit for a customer only once.
So if the Data were
SELECT 'SDC',1234,2020,DATE '2020-3-21' FROM DUAL UNION ALL
SELECT 'SDC',1234,2020,DATE '2020-3-23' FROM DUAL UNION ALL
SELECT 'ELC',1234,2020,DATE '2020-3-24' FROM DUAL UNION ALL
SELECT 'ELC',1234,2020,DATE '2020-3-25' FROM DUAL UNION ALL
SELECT 'SDC',1234,2020,DATE '2020-3-28' FROM DUAL
The Results should be:
Sector Customers CNT
SDC 1
ELC 1
It doesn't matter that the Customer went to Sector SDC 3 times total and ELC 2 Times.
What matters is after going to SDC on 3/21/2020, they had another visit to any Sector. (This is what should give SDC the Credit for his customer_ID
The Visit to Sector ELC on 3/24/2020, and then any visit after that, is what should give ELC credit for Customer_ID 1234 as well.
I hope this helps to clear up my requirements.
I took your code and found that I needed to add a "COUNT DISTINCT" in order to prevent SDC and ELC from getting credit more than once for a single customer.
WITH SAMPLE(SECTOR, CUSTOMER_ID, FISCALYEAR, DATETIME)
AS (
SELECT 'SDC',1234,2020, '2020-3-21' UNION ALL
SELECT 'SDC',1234,2020, '2020-3-23' UNION ALL
SELECT 'ELC',1234,2020, '2020-3-24' UNION ALL
SELECT 'ELC',1234,2020, '2020-3-25' UNION ALL
SELECT 'SDC',1234,2020, '2020-3-28'
)
SELECT T.SECTOR,
COUNT(DISTINCT T.CUSTOMER_ID) AS CNT
FROM (
SELECT SECTOR,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATETIME) = COUNT(*) OVER(PARTITION BY CUSTOMER_ID) THEN NULL
ELSE CUSTOMER_ID
END CUSTOMER_ID
FROM SAMPLE
) T
GROUP BY SECTOR
ORDER BY SECTOR
SECTOR CNT
ELC 1
SDC 1
This is an ingenious piece of code. I have use the Row_Number Function before, but never in this way.
Thank you so much for your help!!
Sincerely,
Tyler
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 29 01:16:31 CDT 2024
|