Mixing
![QGIS: list of coordinates to polygon [duplicate]](https://lh6.googleusercontent.com/-lBG_D1yi7Z8/AAAAAAAAAAI/AAAAAAAAAHA/kwX564gJ5fM/s72-c/photo.jpg?sz=32)
How to join three subqueries and count the intersection correctly?
I would like to find out how many loyal users I have. The definition of that is that a user needs to have logged in at least once every rolling month.
If the user has logged in once between 2018-08-21 and 2018-09-21 and
also once between 2018-09-21 and 2018-10-21 and also once between
2018-10-21 and 2018-11-21 then this is a loyal user.
I thought the best to achieve that is to join these three time defined subqueries. But I'm stuck with how to count it? Each subquery shows a different count. Do I have to consider the lowest value as the intersection? Or am I doing it entirely wrong?
select p.country, round(SUM(a.GBytes+b.GBytes+c.GBytes), 2) as `Traffic`, a.Count, b.Count , c.Count
from (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-08-21 22:13:54.286223' AND '2018-09-21 22:13:54.286223')
GROUP BY username
) a join (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-09-21 22:13:54.286223' AND '2018-10-21 22:13:54.286223')
GROUP BY username
) b on a.username = b.username
join (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-10-21 22:13:54.286223' AND '2018-11-21 22:13:54.286223')
GROUP BY username
) c on b.username = c.username
join cache_db.global_ip p on p.ip = a.IP
join cache_db.global_ip p2 on p2.ip = b.IP
join cache_db.global_ip p3 on p3.ip = c.IP
group by country;
Result:
sql mariadb
asked Nov 22 '18 at 7:23
HoumanHouman
26.2k60196360
add a comment |
I would like to find out how many loyal users I have. The definition of that is that a user needs to have logged in at least once every rolling month.
If the user has logged in once between 2018-08-21 and 2018-09-21 and
also once between 2018-09-21 and 2018-10-21 and also once between
2018-10-21 and 2018-11-21 then this is a loyal user.
I thought the best to achieve that is to join these three time defined subqueries. But I'm stuck with how to count it? Each subquery shows a different count. Do I have to consider the lowest value as the intersection? Or am I doing it entirely wrong?
select p.country, round(SUM(a.GBytes+b.GBytes+c.GBytes), 2) as `Traffic`, a.Count, b.Count , c.Count
from (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-08-21 22:13:54.286223' AND '2018-09-21 22:13:54.286223')
GROUP BY username
) a join (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-09-21 22:13:54.286223' AND '2018-10-21 22:13:54.286223')
GROUP BY username
) b on a.username = b.username
join (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-10-21 22:13:54.286223' AND '2018-11-21 22:13:54.286223')
GROUP BY username
) c on b.username = c.username
join cache_db.global_ip p on p.ip = a.IP
join cache_db.global_ip p2 on p2.ip = b.IP
join cache_db.global_ip p3 on p3.ip = c.IP
group by country;
Result:
sql mariadb
asked Nov 22 '18 at 7:23
HoumanHouman
26.2k60196360
add a comment |
I would like to find out how many loyal users I have. The definition of that is that a user needs to have logged in at least once every rolling month.
If the user has logged in once between 2018-08-21 and 2018-09-21 and
also once between 2018-09-21 and 2018-10-21 and also once between
2018-10-21 and 2018-11-21 then this is a loyal user.
I thought the best to achieve that is to join these three time defined subqueries. But I'm stuck with how to count it? Each subquery shows a different count. Do I have to consider the lowest value as the intersection? Or am I doing it entirely wrong?
select p.country, round(SUM(a.GBytes+b.GBytes+c.GBytes), 2) as `Traffic`, a.Count, b.Count , c.Count
from (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-08-21 22:13:54.286223' AND '2018-09-21 22:13:54.286223')
GROUP BY username
) a join (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-09-21 22:13:54.286223' AND '2018-10-21 22:13:54.286223')
GROUP BY username
) b on a.username = b.username
join (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-10-21 22:13:54.286223' AND '2018-11-21 22:13:54.286223')
GROUP BY username
) c on b.username = c.username
join cache_db.global_ip p on p.ip = a.IP
join cache_db.global_ip p2 on p2.ip = b.IP
join cache_db.global_ip p3 on p3.ip = c.IP
group by country;
Result:
sql mariadb
asked Nov 22 '18 at 7:23
HoumanHouman
26.2k60196360
I would like to find out how many loyal users I have. The definition of that is that a user needs to have logged in at least once every rolling month.
If the user has logged in once between 2018-08-21 and 2018-09-21 and
also once between 2018-09-21 and 2018-10-21 and also once between
2018-10-21 and 2018-11-21 then this is a loyal user.
I thought the best to achieve that is to join these three time defined subqueries. But I'm stuck with how to count it? Each subquery shows a different count. Do I have to consider the lowest value as the intersection? Or am I doing it entirely wrong?
select p.country, round(SUM(a.GBytes+b.GBytes+c.GBytes), 2) as `Traffic`, a.Count, b.Count , c.Count
from (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-08-21 22:13:54.286223' AND '2018-09-21 22:13:54.286223')
GROUP BY username
) a join (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-09-21 22:13:54.286223' AND '2018-10-21 22:13:54.286223')
GROUP BY username
) b on a.username = b.username
join (
SELECT username,
SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
(SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
count(username) as Count
FROM radacct
WHERE (`acctstarttime` BETWEEN '2018-10-21 22:13:54.286223' AND '2018-11-21 22:13:54.286223')
GROUP BY username
) c on b.username = c.username
join cache_db.global_ip p on p.ip = a.IP
join cache_db.global_ip p2 on p2.ip = b.IP
join cache_db.global_ip p3 on p3.ip = c.IP
group by country;
Result:
sql mariadb
sql mariadb
asked Nov 22 '18 at 7:23
HoumanHouman
26.2k60196360
asked Nov 22 '18 at 7:23
HoumanHouman
26.2k60196360
asked Nov 22 '18 at 7:23
HoumanHouman
26.2k60196360
asked Nov 22 '18 at 7:23
HoumanHouman
26.2k60196360
asked Nov 22 '18 at 7:23
HoumanHouman
26.2k60196360
26.2k60196360
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Since you only care if a user has logged in at least once per time period, there is no need to perform counts per user for each time period. Any user who has connected in the given time periods will appear in table a, b or c respectively and since you are JOINing them together on the username field, only users who have logged in in all three time periods will appear in the result set. Thus a simple COUNT(*) in your outer query (in place of a.Count, b.Count , c.Count) will give you the result you want.
If the same username can be used in different countries, then you will need to group by username and country in each of the subqueries and join on username and country as well.
answered Nov 22 '18 at 7:37
NickNick
32.8k121942
Thanks, this is true but depending on whichcountryfield I pick, I get different countries:select p3.country, ..., count(*)vsselect p1.country, ..., count(*)vsselect p.country, ..., count(*). This is somehow the same intersection problem. I get different countries for the same rows.
– Houman
Nov 22 '18 at 7:54
@Houman that would seem to imply that the same username can be used in different countries? In that case I think you would need to group by username and country in each of the subqueries and join on username and country as well.
– Nick
Nov 22 '18 at 12:30
That was exactly the problem. Thank you so much. I was working on this problem for so many days!!!
– Houman
Nov 22 '18 at 23:04
Cool - glad I could help. I'll update the answer so that it includes the comment.
– Nick
Nov 22 '18 at 23:48
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%2f53425767%2fhow-to-join-three-subqueries-and-count-the-intersection-correctly%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
Since you only care if a user has logged in at least once per time period, there is no need to perform counts per user for each time period. Any user who has connected in the given time periods will appear in table a, b or c respectively and since you are JOINing them together on the username field, only users who have logged in in all three time periods will appear in the result set. Thus a simple COUNT(*) in your outer query (in place of a.Count, b.Count , c.Count) will give you the result you want.
If the same username can be used in different countries, then you will need to group by username and country in each of the subqueries and join on username and country as well.
answered Nov 22 '18 at 7:37
NickNick
32.8k121942
Thanks, this is true but depending on whichcountryfield I pick, I get different countries:select p3.country, ..., count(*)vsselect p1.country, ..., count(*)vsselect p.country, ..., count(*). This is somehow the same intersection problem. I get different countries for the same rows.
– Houman
Nov 22 '18 at 7:54
@Houman that would seem to imply that the same username can be used in different countries? In that case I think you would need to group by username and country in each of the subqueries and join on username and country as well.
– Nick
Nov 22 '18 at 12:30
That was exactly the problem. Thank you so much. I was working on this problem for so many days!!!
– Houman
Nov 22 '18 at 23:04
Cool - glad I could help. I'll update the answer so that it includes the comment.
– Nick
Nov 22 '18 at 23:48
add a comment |
Since you only care if a user has logged in at least once per time period, there is no need to perform counts per user for each time period. Any user who has connected in the given time periods will appear in table a, b or c respectively and since you are JOINing them together on the username field, only users who have logged in in all three time periods will appear in the result set. Thus a simple COUNT(*) in your outer query (in place of a.Count, b.Count , c.Count) will give you the result you want.
If the same username can be used in different countries, then you will need to group by username and country in each of the subqueries and join on username and country as well.
answered Nov 22 '18 at 7:37
NickNick
32.8k121942
Thanks, this is true but depending on whichcountryfield I pick, I get different countries:select p3.country, ..., count(*)vsselect p1.country, ..., count(*)vsselect p.country, ..., count(*). This is somehow the same intersection problem. I get different countries for the same rows.
– Houman
Nov 22 '18 at 7:54
@Houman that would seem to imply that the same username can be used in different countries? In that case I think you would need to group by username and country in each of the subqueries and join on username and country as well.
– Nick
Nov 22 '18 at 12:30
That was exactly the problem. Thank you so much. I was working on this problem for so many days!!!
– Houman
Nov 22 '18 at 23:04
Cool - glad I could help. I'll update the answer so that it includes the comment.
– Nick
Nov 22 '18 at 23:48
add a comment |
Since you only care if a user has logged in at least once per time period, there is no need to perform counts per user for each time period. Any user who has connected in the given time periods will appear in table a, b or c respectively and since you are JOINing them together on the username field, only users who have logged in in all three time periods will appear in the result set. Thus a simple COUNT(*) in your outer query (in place of a.Count, b.Count , c.Count) will give you the result you want.
If the same username can be used in different countries, then you will need to group by username and country in each of the subqueries and join on username and country as well.
answered Nov 22 '18 at 7:37
NickNick
32.8k121942
Since you only care if a user has logged in at least once per time period, there is no need to perform counts per user for each time period. Any user who has connected in the given time periods will appear in table a, b or c respectively and since you are JOINing them together on the username field, only users who have logged in in all three time periods will appear in the result set. Thus a simple COUNT(*) in your outer query (in place of a.Count, b.Count , c.Count) will give you the result you want.
If the same username can be used in different countries, then you will need to group by username and country in each of the subqueries and join on username and country as well.
answered Nov 22 '18 at 7:37
NickNick
32.8k121942
edited Nov 22 '18 at 23:49
answered Nov 22 '18 at 7:37
NickNick
32.8k121942
answered Nov 22 '18 at 7:37
NickNick
32.8k121942
answered Nov 22 '18 at 7:37
NickNick
32.8k121942
32.8k121942
Thanks, this is true but depending on whichcountryfield I pick, I get different countries:select p3.country, ..., count(*)vsselect p1.country, ..., count(*)vsselect p.country, ..., count(*). This is somehow the same intersection problem. I get different countries for the same rows.
– Houman
Nov 22 '18 at 7:54
@Houman that would seem to imply that the same username can be used in different countries? In that case I think you would need to group by username and country in each of the subqueries and join on username and country as well.
– Nick
Nov 22 '18 at 12:30
That was exactly the problem. Thank you so much. I was working on this problem for so many days!!!
– Houman
Nov 22 '18 at 23:04
Cool - glad I could help. I'll update the answer so that it includes the comment.
– Nick
Nov 22 '18 at 23:48
add a comment |
Thanks, this is true but depending on whichcountryfield I pick, I get different countries:select p3.country, ..., count(*)vsselect p1.country, ..., count(*)vsselect p.country, ..., count(*). This is somehow the same intersection problem. I get different countries for the same rows.
– Houman
Nov 22 '18 at 7:54
@Houman that would seem to imply that the same username can be used in different countries? In that case I think you would need to group by username and country in each of the subqueries and join on username and country as well.
– Nick
Nov 22 '18 at 12:30
That was exactly the problem. Thank you so much. I was working on this problem for so many days!!!
– Houman
Nov 22 '18 at 23:04
Cool - glad I could help. I'll update the answer so that it includes the comment.
– Nick
Nov 22 '18 at 23:48
Thanks, this is true but depending on which
country field I pick, I get different countries: select p3.country, ..., count(*) vs select p1.country, ..., count(*) vs select p.country, ..., count(*). This is somehow the same intersection problem. I get different countries for the same rows.– Houman
Nov 22 '18 at 7:54
Thanks, this is true but depending on which
country field I pick, I get different countries: select p3.country, ..., count(*) vs select p1.country, ..., count(*) vs select p.country, ..., count(*). This is somehow the same intersection problem. I get different countries for the same rows.– Houman
Nov 22 '18 at 7:54
@Houman that would seem to imply that the same username can be used in different countries? In that case I think you would need to group by username and country in each of the subqueries and join on username and country as well.
– Nick
Nov 22 '18 at 12:30
@Houman that would seem to imply that the same username can be used in different countries? In that case I think you would need to group by username and country in each of the subqueries and join on username and country as well.
– Nick
Nov 22 '18 at 12:30
That was exactly the problem. Thank you so much. I was working on this problem for so many days!!!
– Houman
Nov 22 '18 at 23:04
That was exactly the problem. Thank you so much. I was working on this problem for so many days!!!
– Houman
Nov 22 '18 at 23:04
Cool - glad I could help. I'll update the answer so that it includes the comment.
– Nick
Nov 22 '18 at 23:48
Cool - glad I could help. I'll update the answer so that it includes the comment.
– Nick
Nov 22 '18 at 23:48
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%2f53425767%2fhow-to-join-three-subqueries-and-count-the-intersection-correctly%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
