汇总一周的统计记录

8tntrjer 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(228)

我有一张有以下信息的table。

Table: DETAILS
id     received     site
(PK)   timestamp    varchar

每天有几千个条目，站点信息是硬编码的（.com、def.com等）。我想得到上周每天点击量的摘要，这样的结果是：

site     count(11/04)   count(10/04)    count(09/04) count(08/04)     
abc.com  300            320             310          320
def.com  200            210             200          220
ghi.com  300            320             310          320
jkl.com  200            210             200          220

或：

received  abc.com       def.com          ghi.com     jkl.com
11/04     300           320              310         320
10/04     200           210              200         220
09/04     300           320              310         320
08/04     200           210              200         220
07/04     300           320              310         320

谢谢大家。

mysql mariadb pivot-table

来源：https://stackoverflow.com/questions/49782188/summarizing-records-of-a-week-of-statistics

1条答案

按热度按时间

js4nwp541#

您可以使用一个准备好的语句生成查询，例如上周的语句：

-- Generate Query for last week (from Monday to Sunday)
-- store query in @myQuery
--
SELECT CONCAT(
'SELECT site,\n',
'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())-1) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())-1) day,'%d/%m'),')`,\n'
'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+0) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+0) day,'%d/%m'),')`,\n'
'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+1) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+1) day,'%d/%m'),')`,\n'
'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+2) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+2) day,'%d/%m'),')`,\n'
'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+3) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+3) day,'%d/%m'),')`,\n'
'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+4) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+4) day,'%d/%m'),')`,\n'
'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+5) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+5) day,'%d/%m'),')`\n',
'FROM yourTable\n',
'WHERE received BETWEEN \'',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+5) day,'%Y-%m-%d 00:00:00'),'\' AND \'',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())-1) day,'%Y-%m-%d 23:59:59'),'\'\n',
'GROUP by site\n',
'ORDER by site;'
) INTO @myQuery;

-- Prepare and execute Query
--
PREPARE stmt FROM @myQuery;
EXECUTE stmt;

-- Free Prepare
--
DEALLOCATE PREPARE stmt;

结果

+-----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| site      | count(08/04) | count(07/04) | count(06/04) | count(05/04) | count(04/04) | count(03/04) | count(02/04) |
+-----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| abc.de    | 0            | 0            | 0            | 0            | 0            | 0            | 2            |
| def.at    | 0            | 0            | 0            | 0            | 2            | 0            | 0            |
| first.net | 0            | 0            | 0            | 0            | 0            | 0            | 1            |
| last.org  | 1            | 0            | 0            | 0            | 0            | 0            | 0            |
+-----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+

样品

MariaDB [test]> -- show table values
MariaDB [test]> --
MariaDB [test]> SELECT * from yourTable;
+----+---------------------+------------+
| id | received            | site       |
+----+---------------------+------------+
|  1 | 2018-04-01 23:59:59 | before.com |
|  2 | 2018-04-02 00:00:00 | first.net  |
|  5 | 2018-04-02 03:00:00 | abc.de     |
|  6 | 2018-04-02 04:12:00 | abc.de     |
|  7 | 2018-04-04 07:00:00 | def.at     |
|  8 | 2018-04-04 07:07:00 | def.at     |
|  9 | 2018-04-08 23:59:59 | last.org   |
| 10 | 2018-04-09 00:00:00 | after.ch   |
+----+---------------------+------------+
8 rows in set (0.00 sec)

MariaDB [test]> 
MariaDB [test]> -- Generate Query for last week (from Monday to Sunday)
MariaDB [test]> -- store query in @myQuery
MariaDB [test]> --
MariaDB [test]> SELECT CONCAT(
    -> 'SELECT site,\n',
    -> 'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())-1) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())-1) day,'%d/%m'),')`,\n'
    -> 'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+0) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+0) day,'%d/%m'),')`,\n'
    -> 'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+1) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+1) day,'%d/%m'),')`,\n'
    -> 'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+2) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+2) day,'%d/%m'),')`,\n'
    -> 'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+3) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+3) day,'%d/%m'),')`,\n'
    -> 'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+4) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+4) day,'%d/%m'),')`,\n'
    -> 'sum(if(date(received) = \'', DATE(NOW() - INTERVAL (DAYOFWEEK(NOW())+5) day),'\',1,0)) as `count(',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+5) day,'%d/%m'),')`\n',
    -> 'FROM yourTable\n',
    -> 'WHERE received BETWEEN \'',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())+5) day,'%Y-%m-%d 00:00:00'),'\' AND \'',DATE_FORMAT(NOW() - INTERVAL (DAYOFWEEK(NOW())-1) day,'%Y-%m-%d 23:59:59'),'\'\n',
    -> 'GROUP by site\n',
    -> 'ORDER by site;'
    -> ) INTO @myQuery;
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> 
MariaDB [test]> 
MariaDB [test]> -- Show query - only to review
MariaDB [test]> --
MariaDB [test]> SELECT @myQuery;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @myQuery                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT site,
sum(if(date(received) = '2018-04-08',1,0)) as `count(08/04)`,
sum(if(date(received) = '2018-04-07',1,0)) as `count(07/04)`,
sum(if(date(received) = '2018-04-06',1,0)) as `count(06/04)`,
sum(if(date(received) = '2018-04-05',1,0)) as `count(05/04)`,
sum(if(date(received) = '2018-04-04',1,0)) as `count(04/04)`,
sum(if(date(received) = '2018-04-03',1,0)) as `count(03/04)`,
sum(if(date(received) = '2018-04-02',1,0)) as `count(02/04)`
FROM yourTable
WHERE received BETWEEN '2018-04-02 00:00:00' AND '2018-04-08 23:59:59'
GROUP by site
ORDER by site; |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> 
MariaDB [test]> 
MariaDB [test]> -- Prepare and execute Query
MariaDB [test]> --
MariaDB [test]> PREPARE stmt FROM @myQuery;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [test]> EXECUTE stmt;
+-----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| site      | count(08/04) | count(07/04) | count(06/04) | count(05/04) | count(04/04) | count(03/04) | count(02/04) |
+-----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| abc.de    | 0            | 0            | 0            | 0            | 0            | 0            | 2            |
| def.at    | 0            | 0            | 0            | 0            | 2            | 0            | 0            |
| first.net | 0            | 0            | 0            | 0            | 0            | 0            | 1            |
| last.org  | 1            | 0            | 0            | 0            | 0            | 0            | 0            |
+-----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
4 rows in set (0.00 sec)

MariaDB [test]> 
MariaDB [test]> -- Free Prepare
MariaDB [test]> --
MariaDB [test]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]>

赞(0）回复(0）举报 2021-06-24

我来回答

汇总一周的统计记录

1条答案

相关问题

热门标签

最新问答