在同一查询中选择计数ip和计数DISTINCT ip [英] Select Count of ip and Count of DISTINCT ip in same query

问题描述

 表名：counter 
 
 id | datetime | url | ip 
 ------------------------------------------- 
 1 | 2013-04-12 13:27:09 | url1 | ip01 
 2 | 2013-04-13 10:55:43 | url2 | ip02 
 3 | 2013-04-14 11:14:12 | url1 | ip03 
 4 | 2013-04-15 23:23:55 | url2 | ip04 
 5 | 2013-05-02 08:34:44 | url1 | ip03 
  

使用智能 SELECT 查询，我想要获取3列：

 
不同的URL |计数ip |计数不同的ip 
 |为每个独特的url |为每个不同的url 
 ------------------------------------------ ------------------------- 
 url1 | 3 | 2 
 url2 | 2 | 2 
 

我在下面提出了我的查询在这个解决方案的帮助下，在stackoverflow中这给了我需要的前两列：

  SELECT URL，COUNT（*）AS total_ip FROM计数器GROUP BY url ORDER BY total_ip DESC 
  

但是，我怎样才能将第三列也添加到我的查询？

你能帮我吗？

解决方案

正如您自己提到的，您可以使用 DISTINCT 关键字来区分 URL 秒。使用 COUNT 来计算 IP 和 COUNT（DISTINCT ID）为每个 URL s

获得不同的 IP

试试这个：

  SELECT DISTINCT URL AS D_URL 
，COUNT（IP）AS IP 
，COUNT （DISTINCT IP）AS D_IP 
 FROM counter 
 GROUP BY URL 
  

输出：

 ╔══════════
║ D_URL║IP║D_IP║
╠═════════════════
║url1║3║2║
║url2║2 ║2║
╚═══════════════
  

查看此SQLFiddle

I have a table structure like this:

TABLE NAME : counter

id  |        datetime       |  url  | ip
-------------------------------------------
1   |2013-04-12 13:27:09    | url1  | ip01
2   |2013-04-13 10:55:43    | url2  | ip02
3   |2013-04-14 11:14:12    | url1  | ip03
4   |2013-04-15 23:23:55    | url2  | ip04
5   |2013-05-02 08:34:44    | url1  | ip03

With a smart SELECT query, I want to fetch 3 columns that are:

Distinct URL    |    count of ip            |    count of distinct ip
                |    for each distinct url  |    for each distinct url  
-------------------------------------------------------------------
url1            |    3                      |    2    
url2            |    2                      |    2    

I came up with my query below by the help of this solution in stackoverflow which gives me the first 2 columns that I need:

SELECT url, COUNT(*) AS total_ip FROM counter GROUP BY url ORDER BY total_ip DESC

But how can I add the 3rd column also into my query?

Can you please help me?

解决方案

As you mentioned yourself, you can use DISTINCT keyword to get distinct URLs. Use COUNT to get count of IP and COUNT(DISTINCT ID) to get distinct IP for each URLs

Try this:

SELECT DISTINCT URL AS D_URL
      ,COUNT(IP) AS IP
      ,COUNT(DISTINCT IP) AS D_IP
FROM counter
GROUP BY URL

Output:

╔═══════╦════╦══════╗
║ D_URL ║ IP ║ D_IP ║
╠═══════╬════╬══════╣
║ url1  ║  3 ║    2 ║
║ url2  ║  2 ║    2 ║
╚═══════╩════╩══════╝

See this SQLFiddle

这篇关于在同一查询中选择计数ip和计数DISTINCT ip的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！