SQL query needed - Counting 365 days backwards The 2019 Stack Overflow Developer Survey Results Are InInserting multiple rows in a single SQL query?MySQL Query GROUP BY day / month / yearCount(*) vs Count(1) - SQL ServerHow do I count occurrences by day in SQL?SQL query for finding records where count > 1SQL nested query with count and partitionSQL count number of users every 7 daysreturn values within the last 365 counting from newest date ORACLE SQLOracle SQL rank queryHow to have the rolling distinct count of each day for past three days in Oracle SQL?
Accepted by European university, rejected by all American ones I applied to? Possible reasons?
Is it ok to offer lower paid work as a trial period before negotiating for a full-time job?
Can there be female White Walkers?
What is this sharp, curved notch on my knife for?
What's the name of these plastic connectors
Match Roman Numerals
What does Linus Torvalds mean when he says that Git "never ever" tracks a file?
Button changing its text & action. Good or terrible?
What information about me do stores get via my credit card?
Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?
Are there any other methods to apply to solving simultaneous equations?
How to display lines in a file like ls displays files in a directory?
Can you cast a spell on someone in the Ethereal Plane, if you are on the Material Plane and have the True Seeing spell active?
Identify boardgame from Big movie
Is it okay to consider publishing in my first year of PhD?
Can I have a signal generator on while it's not connected?
Falsification in Math vs Science
What do hard-Brexiteers want with respect to the Irish border?
APIPA and LAN Broadcast Domain
Is Cinnamon a desktop environment or a window manager? (Or both?)
What could be the right powersource for 15 seconds lifespan disposable giant chainsaw?
How do PCB vias affect signal quality?
Why didn't the Event Horizon Telescope team mention Sagittarius A*?
Why doesn't shell automatically fix "useless use of cat"?
SQL query needed - Counting 365 days backwards
The 2019 Stack Overflow Developer Survey Results Are InInserting multiple rows in a single SQL query?MySQL Query GROUP BY day / month / yearCount(*) vs Count(1) - SQL ServerHow do I count occurrences by day in SQL?SQL query for finding records where count > 1SQL nested query with count and partitionSQL count number of users every 7 daysreturn values within the last 365 counting from newest date ORACLE SQLOracle SQL rank queryHow to have the rolling distinct count of each day for past three days in Oracle SQL?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have searched the forum many times but couldn't find a solution for my situation. I am working with an Oracle database.
I have a table with all Order Numbers and Customer Numbers by Day. It looks like this:
Day | Customer Nbr | Order Nbr
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....
Now I need a SQL Query which gives me a table by day and Customer Nbr and counts the number of unique Order Numbers within the last 365 days starting from column 1.
For the example above the resulting table should look like:
Day | Customer Nbr | Order Cnt
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1
sql oracle
add a comment |
I have searched the forum many times but couldn't find a solution for my situation. I am working with an Oracle database.
I have a table with all Order Numbers and Customer Numbers by Day. It looks like this:
Day | Customer Nbr | Order Nbr
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....
Now I need a SQL Query which gives me a table by day and Customer Nbr and counts the number of unique Order Numbers within the last 365 days starting from column 1.
For the example above the resulting table should look like:
Day | Customer Nbr | Order Cnt
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1
sql oracle
1
can you please add a bigger dataset for data and output?
– Hijesh Vl
Mar 8 at 9:41
1
How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?
– Joakim Danielson
Mar 8 at 9:41
I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.
– Kris0603
Mar 8 at 10:29
I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03
– Kris0603
Mar 8 at 10:38
add a comment |
I have searched the forum many times but couldn't find a solution for my situation. I am working with an Oracle database.
I have a table with all Order Numbers and Customer Numbers by Day. It looks like this:
Day | Customer Nbr | Order Nbr
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....
Now I need a SQL Query which gives me a table by day and Customer Nbr and counts the number of unique Order Numbers within the last 365 days starting from column 1.
For the example above the resulting table should look like:
Day | Customer Nbr | Order Cnt
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1
sql oracle
I have searched the forum many times but couldn't find a solution for my situation. I am working with an Oracle database.
I have a table with all Order Numbers and Customer Numbers by Day. It looks like this:
Day | Customer Nbr | Order Nbr
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....
Now I need a SQL Query which gives me a table by day and Customer Nbr and counts the number of unique Order Numbers within the last 365 days starting from column 1.
For the example above the resulting table should look like:
Day | Customer Nbr | Order Cnt
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1
sql oracle
sql oracle
edited Mar 10 at 16:53
halfer
14.8k759117
14.8k759117
asked Mar 8 at 9:35
Kris0603Kris0603
1
1
1
can you please add a bigger dataset for data and output?
– Hijesh Vl
Mar 8 at 9:41
1
How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?
– Joakim Danielson
Mar 8 at 9:41
I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.
– Kris0603
Mar 8 at 10:29
I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03
– Kris0603
Mar 8 at 10:38
add a comment |
1
can you please add a bigger dataset for data and output?
– Hijesh Vl
Mar 8 at 9:41
1
How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?
– Joakim Danielson
Mar 8 at 9:41
I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.
– Kris0603
Mar 8 at 10:29
I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03
– Kris0603
Mar 8 at 10:38
1
1
can you please add a bigger dataset for data and output?
– Hijesh Vl
Mar 8 at 9:41
can you please add a bigger dataset for data and output?
– Hijesh Vl
Mar 8 at 9:41
1
1
How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?
– Joakim Danielson
Mar 8 at 9:41
How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?
– Joakim Danielson
Mar 8 at 9:41
I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.
– Kris0603
Mar 8 at 10:29
I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.
– Kris0603
Mar 8 at 10:29
I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03
– Kris0603
Mar 8 at 10:38
I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03
– Kris0603
Mar 8 at 10:38
add a comment |
3 Answers
3
active
oldest
votes
One method is to generate values for all days of interest for each customer and then use a correlated subquery:
with dates as (
select date '2019-01-01' + rownum as dte from dual
connect by date '2019-01-01' + rownum < sysdate
)
select d.dte, t.customer_nbr,
(select count(*)
from t t2
where t2.customer_nbr = t.customer_nbr and
t2.day <= t.dte and
t2.date > t.dte - 365
) as order_cnt
from dates d cross join
(select distinct customer_nbr from t) ;
add a comment |
Edit:
I've just seen you clarify the question, which I've interpreted to mean:
For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...
Updated Answer:
For each customer, we count the number of records between the order day, and 365 days before it...
WITH yourTable AS
(
SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
UNION ALL
SELECT SYSDATE - 2, 'Alex' FROM DUAL
UNION ALL
SELECT SYSDATE - 366, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 400, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 500, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Joe'FROM DUAL
UNION ALL
SELECT SYSDATE - 300, 'Chris'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Chris'FROM DUAL
)
SELECT Day, CustomerNbr, OrdersLast365Days
FROM yourTable t
OUTER APPLY
(
SELECT COUNT(1) OrdersLast365Days
FROM yourTable t2
WHERE t.CustomerNbr = t2.CustomerNbr
AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
AND TRUNC(t2.Day) <= TRUNC(t.Day)
)
ORDER BY t.Day DESC, t.CustomerNbr;
If you want to report on just the days you have orders for, then a simple WHERE
clause should be enough:
SELECT Day, CustomerNbr, COUNT(1) OrderCount
FROM <yourTable>
WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
GROUP BY Day, CustomerNbr
ORDER BY Day Desc;
If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:
WITH last365Days AS
(
SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
FROM DUAL CONNECT BY ROWNUM < 365
)
SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
FROM last365Days d
LEFT OUTER JOIN <yourTable> t
ON d.Day = TRUNC(t.Day)
GROUP BY d.Day, t.CustomerNbr
ORDER BY d.Day Desc;
Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?
– Kris0603
Mar 8 at 12:37
No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.
– Alex
Mar 8 at 14:38
I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days
– Alex
Mar 8 at 14:47
add a comment |
I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.
This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed
WITH DATES AS (
SELECT * FROM
(SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
CROSS JOIN
(SELECT DISTINCT CUST_ID FROM MY_TABLE))
SELECT DISTINCT
DATES.DAY,
DATES.CUST_ID,
COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
FROM
DATES
LEFT JOIN
MY_TABLE
ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
ORDER BY DATES.CUST_ID,DATES.DAY;
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55060368%2fsql-query-needed-counting-365-days-backwards%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
One method is to generate values for all days of interest for each customer and then use a correlated subquery:
with dates as (
select date '2019-01-01' + rownum as dte from dual
connect by date '2019-01-01' + rownum < sysdate
)
select d.dte, t.customer_nbr,
(select count(*)
from t t2
where t2.customer_nbr = t.customer_nbr and
t2.day <= t.dte and
t2.date > t.dte - 365
) as order_cnt
from dates d cross join
(select distinct customer_nbr from t) ;
add a comment |
One method is to generate values for all days of interest for each customer and then use a correlated subquery:
with dates as (
select date '2019-01-01' + rownum as dte from dual
connect by date '2019-01-01' + rownum < sysdate
)
select d.dte, t.customer_nbr,
(select count(*)
from t t2
where t2.customer_nbr = t.customer_nbr and
t2.day <= t.dte and
t2.date > t.dte - 365
) as order_cnt
from dates d cross join
(select distinct customer_nbr from t) ;
add a comment |
One method is to generate values for all days of interest for each customer and then use a correlated subquery:
with dates as (
select date '2019-01-01' + rownum as dte from dual
connect by date '2019-01-01' + rownum < sysdate
)
select d.dte, t.customer_nbr,
(select count(*)
from t t2
where t2.customer_nbr = t.customer_nbr and
t2.day <= t.dte and
t2.date > t.dte - 365
) as order_cnt
from dates d cross join
(select distinct customer_nbr from t) ;
One method is to generate values for all days of interest for each customer and then use a correlated subquery:
with dates as (
select date '2019-01-01' + rownum as dte from dual
connect by date '2019-01-01' + rownum < sysdate
)
select d.dte, t.customer_nbr,
(select count(*)
from t t2
where t2.customer_nbr = t.customer_nbr and
t2.day <= t.dte and
t2.date > t.dte - 365
) as order_cnt
from dates d cross join
(select distinct customer_nbr from t) ;
answered Mar 8 at 12:40
Gordon LinoffGordon Linoff
796k37318423
796k37318423
add a comment |
add a comment |
Edit:
I've just seen you clarify the question, which I've interpreted to mean:
For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...
Updated Answer:
For each customer, we count the number of records between the order day, and 365 days before it...
WITH yourTable AS
(
SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
UNION ALL
SELECT SYSDATE - 2, 'Alex' FROM DUAL
UNION ALL
SELECT SYSDATE - 366, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 400, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 500, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Joe'FROM DUAL
UNION ALL
SELECT SYSDATE - 300, 'Chris'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Chris'FROM DUAL
)
SELECT Day, CustomerNbr, OrdersLast365Days
FROM yourTable t
OUTER APPLY
(
SELECT COUNT(1) OrdersLast365Days
FROM yourTable t2
WHERE t.CustomerNbr = t2.CustomerNbr
AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
AND TRUNC(t2.Day) <= TRUNC(t.Day)
)
ORDER BY t.Day DESC, t.CustomerNbr;
If you want to report on just the days you have orders for, then a simple WHERE
clause should be enough:
SELECT Day, CustomerNbr, COUNT(1) OrderCount
FROM <yourTable>
WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
GROUP BY Day, CustomerNbr
ORDER BY Day Desc;
If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:
WITH last365Days AS
(
SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
FROM DUAL CONNECT BY ROWNUM < 365
)
SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
FROM last365Days d
LEFT OUTER JOIN <yourTable> t
ON d.Day = TRUNC(t.Day)
GROUP BY d.Day, t.CustomerNbr
ORDER BY d.Day Desc;
Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?
– Kris0603
Mar 8 at 12:37
No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.
– Alex
Mar 8 at 14:38
I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days
– Alex
Mar 8 at 14:47
add a comment |
Edit:
I've just seen you clarify the question, which I've interpreted to mean:
For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...
Updated Answer:
For each customer, we count the number of records between the order day, and 365 days before it...
WITH yourTable AS
(
SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
UNION ALL
SELECT SYSDATE - 2, 'Alex' FROM DUAL
UNION ALL
SELECT SYSDATE - 366, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 400, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 500, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Joe'FROM DUAL
UNION ALL
SELECT SYSDATE - 300, 'Chris'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Chris'FROM DUAL
)
SELECT Day, CustomerNbr, OrdersLast365Days
FROM yourTable t
OUTER APPLY
(
SELECT COUNT(1) OrdersLast365Days
FROM yourTable t2
WHERE t.CustomerNbr = t2.CustomerNbr
AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
AND TRUNC(t2.Day) <= TRUNC(t.Day)
)
ORDER BY t.Day DESC, t.CustomerNbr;
If you want to report on just the days you have orders for, then a simple WHERE
clause should be enough:
SELECT Day, CustomerNbr, COUNT(1) OrderCount
FROM <yourTable>
WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
GROUP BY Day, CustomerNbr
ORDER BY Day Desc;
If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:
WITH last365Days AS
(
SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
FROM DUAL CONNECT BY ROWNUM < 365
)
SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
FROM last365Days d
LEFT OUTER JOIN <yourTable> t
ON d.Day = TRUNC(t.Day)
GROUP BY d.Day, t.CustomerNbr
ORDER BY d.Day Desc;
Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?
– Kris0603
Mar 8 at 12:37
No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.
– Alex
Mar 8 at 14:38
I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days
– Alex
Mar 8 at 14:47
add a comment |
Edit:
I've just seen you clarify the question, which I've interpreted to mean:
For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...
Updated Answer:
For each customer, we count the number of records between the order day, and 365 days before it...
WITH yourTable AS
(
SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
UNION ALL
SELECT SYSDATE - 2, 'Alex' FROM DUAL
UNION ALL
SELECT SYSDATE - 366, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 400, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 500, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Joe'FROM DUAL
UNION ALL
SELECT SYSDATE - 300, 'Chris'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Chris'FROM DUAL
)
SELECT Day, CustomerNbr, OrdersLast365Days
FROM yourTable t
OUTER APPLY
(
SELECT COUNT(1) OrdersLast365Days
FROM yourTable t2
WHERE t.CustomerNbr = t2.CustomerNbr
AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
AND TRUNC(t2.Day) <= TRUNC(t.Day)
)
ORDER BY t.Day DESC, t.CustomerNbr;
If you want to report on just the days you have orders for, then a simple WHERE
clause should be enough:
SELECT Day, CustomerNbr, COUNT(1) OrderCount
FROM <yourTable>
WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
GROUP BY Day, CustomerNbr
ORDER BY Day Desc;
If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:
WITH last365Days AS
(
SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
FROM DUAL CONNECT BY ROWNUM < 365
)
SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
FROM last365Days d
LEFT OUTER JOIN <yourTable> t
ON d.Day = TRUNC(t.Day)
GROUP BY d.Day, t.CustomerNbr
ORDER BY d.Day Desc;
Edit:
I've just seen you clarify the question, which I've interpreted to mean:
For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...
Updated Answer:
For each customer, we count the number of records between the order day, and 365 days before it...
WITH yourTable AS
(
SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
UNION ALL
SELECT SYSDATE - 2, 'Alex' FROM DUAL
UNION ALL
SELECT SYSDATE - 366, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 400, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 500, 'Alex'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Joe'FROM DUAL
UNION ALL
SELECT SYSDATE - 300, 'Chris'FROM DUAL
UNION ALL
SELECT SYSDATE - 1, 'Chris'FROM DUAL
)
SELECT Day, CustomerNbr, OrdersLast365Days
FROM yourTable t
OUTER APPLY
(
SELECT COUNT(1) OrdersLast365Days
FROM yourTable t2
WHERE t.CustomerNbr = t2.CustomerNbr
AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
AND TRUNC(t2.Day) <= TRUNC(t.Day)
)
ORDER BY t.Day DESC, t.CustomerNbr;
If you want to report on just the days you have orders for, then a simple WHERE
clause should be enough:
SELECT Day, CustomerNbr, COUNT(1) OrderCount
FROM <yourTable>
WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
GROUP BY Day, CustomerNbr
ORDER BY Day Desc;
If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:
WITH last365Days AS
(
SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
FROM DUAL CONNECT BY ROWNUM < 365
)
SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
FROM last365Days d
LEFT OUTER JOIN <yourTable> t
ON d.Day = TRUNC(t.Day)
GROUP BY d.Day, t.CustomerNbr
ORDER BY d.Day Desc;
edited Mar 8 at 14:46
answered Mar 8 at 10:44
AlexAlex
22627
22627
Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?
– Kris0603
Mar 8 at 12:37
No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.
– Alex
Mar 8 at 14:38
I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days
– Alex
Mar 8 at 14:47
add a comment |
Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?
– Kris0603
Mar 8 at 12:37
No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.
– Alex
Mar 8 at 14:38
I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days
– Alex
Mar 8 at 14:47
Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?
– Kris0603
Mar 8 at 12:37
Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?
– Kris0603
Mar 8 at 12:37
No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.
– Alex
Mar 8 at 14:38
No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.
– Alex
Mar 8 at 14:38
I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days
– Alex
Mar 8 at 14:47
I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days
– Alex
Mar 8 at 14:47
add a comment |
I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.
This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed
WITH DATES AS (
SELECT * FROM
(SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
CROSS JOIN
(SELECT DISTINCT CUST_ID FROM MY_TABLE))
SELECT DISTINCT
DATES.DAY,
DATES.CUST_ID,
COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
FROM
DATES
LEFT JOIN
MY_TABLE
ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
ORDER BY DATES.CUST_ID,DATES.DAY;
add a comment |
I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.
This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed
WITH DATES AS (
SELECT * FROM
(SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
CROSS JOIN
(SELECT DISTINCT CUST_ID FROM MY_TABLE))
SELECT DISTINCT
DATES.DAY,
DATES.CUST_ID,
COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
FROM
DATES
LEFT JOIN
MY_TABLE
ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
ORDER BY DATES.CUST_ID,DATES.DAY;
add a comment |
I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.
This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed
WITH DATES AS (
SELECT * FROM
(SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
CROSS JOIN
(SELECT DISTINCT CUST_ID FROM MY_TABLE))
SELECT DISTINCT
DATES.DAY,
DATES.CUST_ID,
COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
FROM
DATES
LEFT JOIN
MY_TABLE
ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
ORDER BY DATES.CUST_ID,DATES.DAY;
I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.
This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed
WITH DATES AS (
SELECT * FROM
(SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
CROSS JOIN
(SELECT DISTINCT CUST_ID FROM MY_TABLE))
SELECT DISTINCT
DATES.DAY,
DATES.CUST_ID,
COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
FROM
DATES
LEFT JOIN
MY_TABLE
ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
ORDER BY DATES.CUST_ID,DATES.DAY;
answered Mar 8 at 16:45
LauDecLauDec
508410
508410
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55060368%2fsql-query-needed-counting-365-days-backwards%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
can you please add a bigger dataset for data and output?
– Hijesh Vl
Mar 8 at 9:41
1
How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?
– Joakim Danielson
Mar 8 at 9:41
I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.
– Kris0603
Mar 8 at 10:29
I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03
– Kris0603
Mar 8 at 10:38