How to Perform Window Partitioning with Conditions (Oracle)
I need to know how to narrow down my partitioning window with a condition. For instance, if I have the following:
SELECT
T1.*,
COUNT(T1.ID) OVER (PARTITION BY ID)
FROM TBL T1
WHERE /* some other conditions */;
I need the ID partition to only be a subset of all the identical ID's that have T1.TYPE = 'J'.
+---------+--------+---------------+--------+
| ID | TYPE | OTH1 | OTH2 |
+---------+--------+---------------+--------+
| 1 | K | 500 | RER |
| 1 | J | 503 | LEL |
| 1 | J | 534 | KEL |
| 2 | J | 536 | NULL |
| 2 | J | 667 | ERT |
| 2 | J | 98 | NULL |
+---------+--------+---------------+--------+
In this data set, I need to only consider my window count if TYPE = J, so for ID = 1, the count should be 2 rather than 3 since the first row is TYPE = K (ID = 2 where the count is 3).
Is this possible?
sql oracle oracle12c database-partitioning
asked Nov 13 '18 at 2:59
NuCradleNuCradle
131110
add a comment |
I need to know how to narrow down my partitioning window with a condition. For instance, if I have the following:
SELECT
T1.*,
COUNT(T1.ID) OVER (PARTITION BY ID)
FROM TBL T1
WHERE /* some other conditions */;
I need the ID partition to only be a subset of all the identical ID's that have T1.TYPE = 'J'.
+---------+--------+---------------+--------+
| ID | TYPE | OTH1 | OTH2 |
+---------+--------+---------------+--------+
| 1 | K | 500 | RER |
| 1 | J | 503 | LEL |
| 1 | J | 534 | KEL |
| 2 | J | 536 | NULL |
| 2 | J | 667 | ERT |
| 2 | J | 98 | NULL |
+---------+--------+---------------+--------+
In this data set, I need to only consider my window count if TYPE = J, so for ID = 1, the count should be 2 rather than 3 since the first row is TYPE = K (ID = 2 where the count is 3).
Is this possible?
sql oracle oracle12c database-partitioning
asked Nov 13 '18 at 2:59
NuCradleNuCradle
131110
What is your expected output?
– Tim Biegeleisen
Nov 13 '18 at 3:00
add a comment |
I need to know how to narrow down my partitioning window with a condition. For instance, if I have the following:
SELECT
T1.*,
COUNT(T1.ID) OVER (PARTITION BY ID)
FROM TBL T1
WHERE /* some other conditions */;
I need the ID partition to only be a subset of all the identical ID's that have T1.TYPE = 'J'.
+---------+--------+---------------+--------+
| ID | TYPE | OTH1 | OTH2 |
+---------+--------+---------------+--------+
| 1 | K | 500 | RER |
| 1 | J | 503 | LEL |
| 1 | J | 534 | KEL |
| 2 | J | 536 | NULL |
| 2 | J | 667 | ERT |
| 2 | J | 98 | NULL |
+---------+--------+---------------+--------+
In this data set, I need to only consider my window count if TYPE = J, so for ID = 1, the count should be 2 rather than 3 since the first row is TYPE = K (ID = 2 where the count is 3).
Is this possible?
sql oracle oracle12c database-partitioning
asked Nov 13 '18 at 2:59
NuCradleNuCradle
131110
I need to know how to narrow down my partitioning window with a condition. For instance, if I have the following:
SELECT
T1.*,
COUNT(T1.ID) OVER (PARTITION BY ID)
FROM TBL T1
WHERE /* some other conditions */;
I need the ID partition to only be a subset of all the identical ID's that have T1.TYPE = 'J'.
+---------+--------+---------------+--------+
| ID | TYPE | OTH1 | OTH2 |
+---------+--------+---------------+--------+
| 1 | K | 500 | RER |
| 1 | J | 503 | LEL |
| 1 | J | 534 | KEL |
| 2 | J | 536 | NULL |
| 2 | J | 667 | ERT |
| 2 | J | 98 | NULL |
+---------+--------+---------------+--------+
In this data set, I need to only consider my window count if TYPE = J, so for ID = 1, the count should be 2 rather than 3 since the first row is TYPE = K (ID = 2 where the count is 3).
Is this possible?
sql oracle oracle12c database-partitioning
sql oracle oracle12c database-partitioning
asked Nov 13 '18 at 2:59
NuCradleNuCradle
131110
asked Nov 13 '18 at 2:59
NuCradleNuCradle
131110
asked Nov 13 '18 at 2:59
NuCradleNuCradle
131110
asked Nov 13 '18 at 2:59
NuCradleNuCradle
131110
asked Nov 13 '18 at 2:59
NuCradleNuCradle
131110
131110
What is your expected output?
– Tim Biegeleisen
Nov 13 '18 at 3:00
add a comment |
What is your expected output?
– Tim Biegeleisen
Nov 13 '18 at 3:00
What is your expected output?
– Tim Biegeleisen
Nov 13 '18 at 3:00
What is your expected output?
– Tim Biegeleisen
Nov 13 '18 at 3:00
add a comment |
1 Answer
1
active
oldest
votes
Perhaps conditional aggregation is what you want here:
SELECT
t.*,
COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID) cnt
FROM TBL t
If you only wanted to display this count for those records actually having the J type, then we could try:
SELECT
t.*,
CASE WHEN TYPE = 'J'
THEN COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID)
ELSE 0 END cnt
FROM TBL t
answered Nov 13 '18 at 3:02
Tim BiegeleisenTim Biegeleisen
219k1388141
That does the job. Thanks.
– NuCradle
Nov 13 '18 at 4:51
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%2f53273152%2fhow-to-perform-window-partitioning-with-conditions-oracle%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Perhaps conditional aggregation is what you want here:
SELECT
t.*,
COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID) cnt
FROM TBL t
If you only wanted to display this count for those records actually having the J type, then we could try:
SELECT
t.*,
CASE WHEN TYPE = 'J'
THEN COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID)
ELSE 0 END cnt
FROM TBL t
answered Nov 13 '18 at 3:02
Tim BiegeleisenTim Biegeleisen
219k1388141
That does the job. Thanks.
– NuCradle
Nov 13 '18 at 4:51
add a comment |
Perhaps conditional aggregation is what you want here:
SELECT
t.*,
COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID) cnt
FROM TBL t
If you only wanted to display this count for those records actually having the J type, then we could try:
SELECT
t.*,
CASE WHEN TYPE = 'J'
THEN COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID)
ELSE 0 END cnt
FROM TBL t
answered Nov 13 '18 at 3:02
Tim BiegeleisenTim Biegeleisen
219k1388141
That does the job. Thanks.
– NuCradle
Nov 13 '18 at 4:51
add a comment |
Perhaps conditional aggregation is what you want here:
SELECT
t.*,
COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID) cnt
FROM TBL t
If you only wanted to display this count for those records actually having the J type, then we could try:
SELECT
t.*,
CASE WHEN TYPE = 'J'
THEN COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID)
ELSE 0 END cnt
FROM TBL t
answered Nov 13 '18 at 3:02
Tim BiegeleisenTim Biegeleisen
219k1388141
Perhaps conditional aggregation is what you want here:
SELECT
t.*,
COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID) cnt
FROM TBL t
If you only wanted to display this count for those records actually having the J type, then we could try:
SELECT
t.*,
CASE WHEN TYPE = 'J'
THEN COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID)
ELSE 0 END cnt
FROM TBL t
answered Nov 13 '18 at 3:02
Tim BiegeleisenTim Biegeleisen
219k1388141
answered Nov 13 '18 at 3:02
Tim BiegeleisenTim Biegeleisen
219k1388141
answered Nov 13 '18 at 3:02
Tim BiegeleisenTim Biegeleisen
219k1388141
answered Nov 13 '18 at 3:02
Tim BiegeleisenTim Biegeleisen
219k1388141
219k1388141
That does the job. Thanks.
– NuCradle
Nov 13 '18 at 4:51
add a comment |
That does the job. Thanks.
– NuCradle
Nov 13 '18 at 4:51
That does the job. Thanks.
– NuCradle
Nov 13 '18 at 4:51
That does the job. Thanks.
– NuCradle
Nov 13 '18 at 4:51
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%2f53273152%2fhow-to-perform-window-partitioning-with-conditions-oracle%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
What is your expected output?
– Tim Biegeleisen
Nov 13 '18 at 3:00