我有2张桌子如下
client_table
date (timestamp)
ip (varchar)
value1 (int)
value2 (int)
ip_address_table
starting_ip (varchar)
ending_ip (varchar)
provider (varchar)
我正在尝试使用ip_address_table表查找client_table条目,以查找每小时的平均时间(如果ip地址在该范围内). (如果不在范围内,请忽略该行)
以下是我想出的
SELECT avg(value1),avg(value2)
FROM client_table
WHERE (INET_ATON(ip) BETWEEN INET_ATON(starting_ip) AND INET_ATON(ending_ip))
GROUP BY DATE(date),HOUR(date)
谢谢
编辑:我的示例数据如下
client_table
"date" "ip" "value1" "value2"
"2012-01-10 06:38:29" "121.1.2.3" "1049" "301"
"2012-01-11 06:41:35" "58.1.2.3" "16453" "794"
"2012-01-12 06:43:50" "101.1.2.3" "1712" "410"
"2012-01-13 06:44:04" "121.1.10.3" "6594" "863"
"2012-01-14 06:45:00" "79.1.2.3" "879" "614"
ip_address_table
"starting_ip" "ending_ip" "provider"
"121.0.0.0" "121.255.255.255" provider1
"79.1.2.0" "79.1.2.255" provider1
....