组合这些 mySQL 查询

发布于 2024-10-08 22:37:20 字数 587 浏览 4 评论 0原文

我需要合并这些查询，以便我返回月份列表，其中包含新的总计和返回的总计（以跟踪新用户和返回用户）。我目前有以下两个疑问。第一个计数为新设备（检测频率），第二个计数为返回。我想输出结果，因此它是一个包含每个月的行的表，然后是包含“新建”和“返回”数据的两列。

SELECT COUNT( DISTINCT (mac) ) AS new,
EXTRACT( MONTH FROM date_time ) AS month
FROM detected_devices
WHERE client_id = 11
AND venue_id = 1
AND detection_frequency = 1
GROUP BY month

UNION ALL

SELECT COUNT( DISTINCT (mac) ) AS returning,
EXTRACT( MONTH FROM date_time ) AS month
FROM detected_devices
WHERE client_id = 11
AND venue_id = 1
AND detection_frequency > 1
GROUP BY month

我环顾四周，但没有找到任何有关如何使用别名来完成此操作的信息。

原文

I need to combine these queries, so i get back a list of months, with a new total, and returning total (to track new and returning users). I currently have these two queries below. The first counts devices which are new (detection freq), the second counts as returning. I want to output the results so it's a table with rows for each month, then two columns with New, and Returning data.

SELECT COUNT( DISTINCT (mac) ) AS new,
EXTRACT( MONTH FROM date_time ) AS month
FROM detected_devices
WHERE client_id = 11
AND venue_id = 1
AND detection_frequency = 1
GROUP BY month

UNION ALL

SELECT COUNT( DISTINCT (mac) ) AS returning,
EXTRACT( MONTH FROM date_time ) AS month
FROM detected_devices
WHERE client_id = 11
AND venue_id = 1
AND detection_frequency > 1
GROUP BY month

I have had a look around but not found any information on how this can be done with aliases.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

猫腻 2024-10-15 22:37:20

这又如何呢？

SELECT t1.month, t1.new, t2.returning FROM (query 1) as t1, (query 2) as t2 where t1.month = t2.month;

（或加入，取决于您的喜好）。

What about this ?

SELECT t1.month, t1.new, t2.returning FROM (query 1) as t1, (query 2) as t2 where t1.month = t2.month;

(or a join, depending on what you prefer).

回复收藏 0 原文

牵强ㄟ 2024-10-15 22:37:20

这只为您提供一行两列，但应该让您了解如何使用别名

SELECT
A.new,
B.returning
FROM
(SELECT COUNT( DISTINCT (mac) ) AS new,
EXTRACT( MONTH FROM date_time ) AS month
FROM detected_devices
WHERE client_id = 11
AND venue_id = 1
AND detection_frequency = 1
GROUP BY month) A,
(SELECT COUNT( DISTINCT (mac) ) AS returning,
EXTRACT( MONTH FROM date_time ) AS month
FROM detected_devices
WHERE client_id = 11
AND venue_id = 1
AND detection_frequency > 1
GROUP BY month) B

This only gives you one row with two columns, but should give you an idea of how you can use aliases

SELECT
A.new,
B.returning
FROM
(SELECT COUNT( DISTINCT (mac) ) AS new,
EXTRACT( MONTH FROM date_time ) AS month
FROM detected_devices
WHERE client_id = 11
AND venue_id = 1
AND detection_frequency = 1
GROUP BY month) A,
(SELECT COUNT( DISTINCT (mac) ) AS returning,
EXTRACT( MONTH FROM date_time ) AS month
FROM detected_devices
WHERE client_id = 11
AND venue_id = 1
AND detection_frequency > 1
GROUP BY month) B

回复收藏 0 原文

~没有更多了~