change references of child elements so the amount of children for parents is the more or less equal
0
I have table item and table container that represents the storage of item (basically parent is container and item is a child)
item --------------------------
item_id | item_type | item_name | container_id
container ---------------------
container_id | container_type
I can calculate the amount of items in each container like this
select container_id, count(item_id) from container
join item on item.container_id = container.container_id
group by container_id
Assuming I have results like this
container_1 | 100
container_2 | 1700
container_3 | 200
container_4 | 1000
container_5 | 2500
I want to move all items from one container to another in order to create containers where the amount of items no more than 2500 and remove other containers
Basically the final result I want to achieve is to get
-- containers 2, 3 are removed
container_1 2000 -- it can container_2, doesn't matter
container_4 1000
container_5 2500
How to achieve that in SQL?
sql postgresql
asked Nov 15 '18 at 9:45
lapotslapots
3,7511362134
add a comment |
0
I have table item and table container that represents the storage of item (basically parent is container and item is a child)
item --------------------------
item_id | item_type | item_name | container_id
container ---------------------
container_id | container_type
I can calculate the amount of items in each container like this
select container_id, count(item_id) from container
join item on item.container_id = container.container_id
group by container_id
Assuming I have results like this
container_1 | 100
container_2 | 1700
container_3 | 200
container_4 | 1000
container_5 | 2500
I want to move all items from one container to another in order to create containers where the amount of items no more than 2500 and remove other containers
Basically the final result I want to achieve is to get
-- containers 2, 3 are removed
container_1 2000 -- it can container_2, doesn't matter
container_4 1000
container_5 2500
How to achieve that in SQL?
sql postgresql
asked Nov 15 '18 at 9:45
lapotslapots
3,7511362134
Do you have to do this in pure SQL? My guess is that you would not a function or proc, at least.
– Tim Biegeleisen
Nov 15 '18 at 9:51
@TimBiegeleisen yeah, using function or procedure is not a problem
– lapots
Nov 15 '18 at 9:59
This is quite a lot of code. Have a go at it yourself and come back with a specific problem - SO is not a freelance, no-cost code writing service. As a suggestion for an algorithm: select all containers where 2500 - container count > 0, ordered by count desc (fill up fullest ones first). Taking containers in order of lowcount to highcount, update container of the lowest (eg container_1) items so they're maximally assigned to the fullest ( container 2) container, until container_2 is full, then move on to putting them in container 4 (next fullest)
– Caius Jard
Nov 15 '18 at 11:34
You need a recursive CTE to solve this. An iterative solution would not be much slower (although iterating in the database using recursive CTEs saves all the round-trip overhead of a non-database solution).
– Gordon Linoff
Nov 15 '18 at 12:17
@CaiusJard I don't expect it as one line of course. I just have no idea how I can tackle that using SQL. Thanks for advice.
– lapots
Nov 15 '18 at 12:34
add a comment |
0
0
0
I have table item and table container that represents the storage of item (basically parent is container and item is a child)
item --------------------------
item_id | item_type | item_name | container_id
container ---------------------
container_id | container_type
I can calculate the amount of items in each container like this
select container_id, count(item_id) from container
join item on item.container_id = container.container_id
group by container_id
Assuming I have results like this
container_1 | 100
container_2 | 1700
container_3 | 200
container_4 | 1000
container_5 | 2500
I want to move all items from one container to another in order to create containers where the amount of items no more than 2500 and remove other containers
Basically the final result I want to achieve is to get
-- containers 2, 3 are removed
container_1 2000 -- it can container_2, doesn't matter
container_4 1000
container_5 2500
How to achieve that in SQL?
sql postgresql
asked Nov 15 '18 at 9:45
lapotslapots
3,7511362134
I have table item and table container that represents the storage of item (basically parent is container and item is a child)
item --------------------------
item_id | item_type | item_name | container_id
container ---------------------
container_id | container_type
I can calculate the amount of items in each container like this
select container_id, count(item_id) from container
join item on item.container_id = container.container_id
group by container_id
Assuming I have results like this
container_1 | 100
container_2 | 1700
container_3 | 200
container_4 | 1000
container_5 | 2500
I want to move all items from one container to another in order to create containers where the amount of items no more than 2500 and remove other containers
Basically the final result I want to achieve is to get
-- containers 2, 3 are removed
container_1 2000 -- it can container_2, doesn't matter
container_4 1000
container_5 2500
How to achieve that in SQL?
sql postgresql
sql postgresql
asked Nov 15 '18 at 9:45
lapotslapots
3,7511362134
asked Nov 15 '18 at 9:45
lapotslapots
3,7511362134
asked Nov 15 '18 at 9:45
lapotslapots
3,7511362134
asked Nov 15 '18 at 9:45
lapotslapots
3,7511362134
asked Nov 15 '18 at 9:45
lapotslapots
3,7511362134
3,7511362134
Do you have to do this in pure SQL? My guess is that you would not a function or proc, at least.
– Tim Biegeleisen
Nov 15 '18 at 9:51
@TimBiegeleisen yeah, using function or procedure is not a problem
– lapots
Nov 15 '18 at 9:59
This is quite a lot of code. Have a go at it yourself and come back with a specific problem - SO is not a freelance, no-cost code writing service. As a suggestion for an algorithm: select all containers where 2500 - container count > 0, ordered by count desc (fill up fullest ones first). Taking containers in order of lowcount to highcount, update container of the lowest (eg container_1) items so they're maximally assigned to the fullest ( container 2) container, until container_2 is full, then move on to putting them in container 4 (next fullest)
– Caius Jard
Nov 15 '18 at 11:34
You need a recursive CTE to solve this. An iterative solution would not be much slower (although iterating in the database using recursive CTEs saves all the round-trip overhead of a non-database solution).
– Gordon Linoff
Nov 15 '18 at 12:17
@CaiusJard I don't expect it as one line of course. I just have no idea how I can tackle that using SQL. Thanks for advice.
– lapots
Nov 15 '18 at 12:34
add a comment |
Do you have to do this in pure SQL? My guess is that you would not a function or proc, at least.
– Tim Biegeleisen
Nov 15 '18 at 9:51
@TimBiegeleisen yeah, using function or procedure is not a problem
– lapots
Nov 15 '18 at 9:59
This is quite a lot of code. Have a go at it yourself and come back with a specific problem - SO is not a freelance, no-cost code writing service. As a suggestion for an algorithm: select all containers where 2500 - container count > 0, ordered by count desc (fill up fullest ones first). Taking containers in order of lowcount to highcount, update container of the lowest (eg container_1) items so they're maximally assigned to the fullest ( container 2) container, until container_2 is full, then move on to putting them in container 4 (next fullest)
– Caius Jard
Nov 15 '18 at 11:34
You need a recursive CTE to solve this. An iterative solution would not be much slower (although iterating in the database using recursive CTEs saves all the round-trip overhead of a non-database solution).
– Gordon Linoff
Nov 15 '18 at 12:17
@CaiusJard I don't expect it as one line of course. I just have no idea how I can tackle that using SQL. Thanks for advice.
– lapots
Nov 15 '18 at 12:34
Do you have to do this in pure SQL? My guess is that you would not a function or proc, at least.
– Tim Biegeleisen
Nov 15 '18 at 9:51
Do you have to do this in pure SQL? My guess is that you would not a function or proc, at least.
– Tim Biegeleisen
Nov 15 '18 at 9:51
@TimBiegeleisen yeah, using function or procedure is not a problem
– lapots
Nov 15 '18 at 9:59
@TimBiegeleisen yeah, using function or procedure is not a problem
– lapots
Nov 15 '18 at 9:59
This is quite a lot of code. Have a go at it yourself and come back with a specific problem - SO is not a freelance, no-cost code writing service. As a suggestion for an algorithm: select all containers where 2500 - container count > 0, ordered by count desc (fill up fullest ones first). Taking containers in order of lowcount to highcount, update container of the lowest (eg container_1) items so they're maximally assigned to the fullest ( container 2) container, until container_2 is full, then move on to putting them in container 4 (next fullest)
– Caius Jard
Nov 15 '18 at 11:34
This is quite a lot of code. Have a go at it yourself and come back with a specific problem - SO is not a freelance, no-cost code writing service. As a suggestion for an algorithm: select all containers where 2500 - container count > 0, ordered by count desc (fill up fullest ones first). Taking containers in order of lowcount to highcount, update container of the lowest (eg container_1) items so they're maximally assigned to the fullest ( container 2) container, until container_2 is full, then move on to putting them in container 4 (next fullest)
– Caius Jard
Nov 15 '18 at 11:34
You need a recursive CTE to solve this. An iterative solution would not be much slower (although iterating in the database using recursive CTEs saves all the round-trip overhead of a non-database solution).
– Gordon Linoff
Nov 15 '18 at 12:17
You need a recursive CTE to solve this. An iterative solution would not be much slower (although iterating in the database using recursive CTEs saves all the round-trip overhead of a non-database solution).
– Gordon Linoff
Nov 15 '18 at 12:17
@CaiusJard I don't expect it as one line of course. I just have no idea how I can tackle that using SQL. Thanks for advice.
– lapots
Nov 15 '18 at 12:34
@CaiusJard I don't expect it as one line of course. I just have no idea how I can tackle that using SQL. Thanks for advice.
– lapots
Nov 15 '18 at 12:34
add a comment |
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53316533%2fchange-references-of-child-elements-so-the-amount-of-children-for-parents-is-the%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53316533%2fchange-references-of-child-elements-so-the-amount-of-children-for-parents-is-the%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
Do you have to do this in pure SQL? My guess is that you would not a function or proc, at least.
– Tim Biegeleisen
Nov 15 '18 at 9:51
@TimBiegeleisen yeah, using function or procedure is not a problem
– lapots
Nov 15 '18 at 9:59
This is quite a lot of code. Have a go at it yourself and come back with a specific problem - SO is not a freelance, no-cost code writing service. As a suggestion for an algorithm: select all containers where 2500 - container count > 0, ordered by count desc (fill up fullest ones first). Taking containers in order of lowcount to highcount, update container of the lowest (eg container_1) items so they're maximally assigned to the fullest ( container 2) container, until container_2 is full, then move on to putting them in container 4 (next fullest)
– Caius Jard
Nov 15 '18 at 11:34
You need a recursive CTE to solve this. An iterative solution would not be much slower (although iterating in the database using recursive CTEs saves all the round-trip overhead of a non-database solution).
– Gordon Linoff
Nov 15 '18 at 12:17
@CaiusJard I don't expect it as one line of course. I just have no idea how I can tackle that using SQL. Thanks for advice.
– lapots
Nov 15 '18 at 12:34