MYSQL针对同一数据的多个组的单个查询

Question

I have two query to get count and sum of rate for unique ip's.

Query one groups by date and query two groups by country

This is the table

DROP TABLE IF EXISTS `stats`;
CREATE TABLE IF NOT EXISTS `stats` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(5) UNSIGNED NOT NULL,
  `country` int(3) UNSIGNED NOT NULL,
  `user_ip` int(50) UNSIGNED NOT NULL,
  `timestamp` int(10) UNSIGNED NOT NULL,
  `rate` int(7) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stats`
--

INSERT INTO `stats` (`id`, `user_id`, `country`, `user_ip`, `timestamp`, `rate`) VALUES
(1, 1, 1, 1111111111, 1489999983, 15000),
(2, 1, 2, 1111111112, 1489999984, 10000),
(3, 1, 1, 1111111111, 1489999985, 10000),
(4, 1, 1, 1111111111, 1490086333, 10000),
(5, 1, 2, 1111111111, 1490086334, 10000),
(6, 1, 1, 1111111121, 1490086335, 10000);

These are the queries I am using to get data

To get sum of rates based on date I use following query

SELECT COUNT(`user_ip`) AS `count`, SUM(`rate`) AS `rate`, `timestamp`  
FROM (
    SELECT DISTINCT `user_ip`, `rate`, `timestamp`
    FROM `stats`.`stats`
    WHERE `user_id`=? `timestamp`>=? AND `timestamp`<=?
    GROUP BY DATE(FROM_UNIXTIME(`timestamp`)),`user_ip`
) c
GROUP BY DATE(FROM_UNIXTIME(`timestamp`))

Result
date        count  rate
20-03-2017   2     25000 
21-03-2017   2     20000

To get sum of rates based on country I use following query

SELECT COUNT(`user_ip`) AS `count`, SUM(`rate`) AS `rate`, `timestamp`, `country`  
FROM (
    SELECT DISTINCT `user_ip`, `rate`, `timestamp`, `country`
    FROM `stats`.`stats`
    WHERE `user_id`=? `timestamp`>=? AND `timestamp`<=?
    GROUP BY DATE(FROM_UNIXTIME(`timestamp`)),`user_ip`
) c
GROUP BY `country`

Result
country    count    rate
1           3       35000
2           1       10000

Since these two query are nearly same and fetches same rows from table is it possible to get both result from single query instead of two query.

Also please suggest if it can be be done in PHP effectively than MYSQL.

Thanks

Answer 1

Try this in php

$country="";
$groupByCondition="DATE(FROM_UNIXTIME(`timestamp`))";

if(/*BasedOnCountry*/){
    $country=", `country`";
    $groupByCondition = "`country`";
}

$query= "SELECT COUNT(`user_ip`) AS `count`, SUM(`rate`) AS `rate`, `timestamp`"+ $country +"  
        FROM (
            SELECT DISTINCT `user_ip`, `rate`, `timestamp`"+ $country +"
            FROM `stats`.`stats`
            WHERE `user_id`=? `timestamp`>=? AND `timestamp`<=?
            GROUP BY DATE(FROM_UNIXTIME(`timestamp`)),`user_ip`
            ) c
        GROUP BY "+ $groupByCondition ;

//execute the query and get the results