订单表order
字段:order_id,user_id,order_total,cdate
统计:在某个时间段内用户重复购买的次数(大于等于2才算重复购买),
这里有种情况要考虑进去,比如2012-03-01至2012-03-31之间,user1只买了一次,而在2012-03-01之前购物买过这次也算重复购买
字段:order_id,user_id,order_total,cdate
统计:在某个时间段内用户重复购买的次数(大于等于2才算重复购买),
这里有种情况要考虑进去,比如2012-03-01至2012-03-31之间,user1只买了一次,而在2012-03-01之前购物买过这次也算重复购买
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0',
`order_total` decimal(15,5) NOT NULL DEFAULT '0.00000',
`cdate` int(11) DEFAULT NULL,
PRIMARY KEY (`order_id`)
)INSERT INTO `orders` (`order_id`, `user_id`, `order_total`, `cdate`) VALUES
(1, 64, '137.04', 1332818403),
(2, 3, '58.00', 1332817679),
(3, 64, '93.99', 1332816160),
(4, 1, '93.99', 1332816039),
(5, 3, '22.00', 1332812556),
(6, 5, '36.00', 1332809122),
(7, 1, '12.00', 1332806097),
(8, 3, '9.00', 1332804724),要得到这样的报表:
Date User_id 重复次数 总共次数 重复购买金额
2012-03-01至2012-03-16 64 1 2 93.99000
2012-03-01至2012-03-16 3 2 3 80.99000
count 用在 having 里。
最后一句话不理解,这里给一个思想吧,自己改:
mysql> select
-> concat(date_format(from_unixtime(min(cdate)), "%Y-%m-%d"), "至", date
_format(from_unixtime(max(cdate)), "%Y-%m-%d")) as data,
-> user_id,
-> count(user_id) - 1 as '重复次数',
-> count(user_id) as '总数',
-> sum(order_total) as '重复购买金额'
-> from orders
-> group by user_id
-> having count(user_id) > 1;
+------------------------+---------+----------+------+--------------+
| data | user_id | 重复次数 | 总数 | 重复购买金额 |
+------------------------+---------+----------+------+--------------+
| 2012-03-27至2012-03-27 | 1 | 1 | 2 | 105.99000 |
| 2012-03-27至2012-03-27 | 3 | 2 | 3 | 89.00000 |
| 2012-03-27至2012-03-27 | 64 | 1 | 2 | 231.03000 |
+------------------------+---------+----------+------+--------------+
3 rows in set (0.00 sec)
| date | 重复次数 | 总数 | 重复购买金额 |
+------------------------+------+--------------+------+
| 2012-03-27至2012-03-27 | 4 | 7 | 426.02000 |
+------------------------+------+--------------+------+
另外对你这个逻辑还是有点疑问,如果之前的总消费数据比你选定区间的数据还要多,算出来不就出现负数了?
这只是一个思路吧,你自己改改。mysql> set @start_time := unix_timestamp("2012-03-01");
Query OK, 0 rows affected (0.00 sec)mysql> set @end_time := unix_timestamp("2012-03-31");
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> select date, between_stat.user_id, between_stat.total - ifnull(before_sta
t.total, 0) as '重复次数', between_stat.total as '总共次数', between_stat.total_
cost - ifnull(before_stat.total_cost, 0) as '重复购买金额'
-> from
-> (
-> select
-> concat(date_format(from_unixtime(min(cdate)), "%Y-%m-%d"), "至",
date_format(from_unixtime(max(cdate)), "%Y-%m-%d")) as date,
-> user_id,
-> count(user_id) as total,
-> sum(order_total) as total_cost
-> from orders
-> where cdate between @start_time and @end_time
-> group by user_id
-> ) as between_stat
->
-> left join
->
-> (
-> select
-> user_id,
-> count(user_id) as total,
-> sum(order_total) as total_cost
-> from orders
-> where cdate < @start_time
-> group by user_id
-> ) as before_stat
->
-> on between_stat.user_id = before_stat.user_id;
+------------------------+---------+----------+----------+--------------+
| date | user_id | 重复次数 | 总共次数 | 重复购买金额 |
+------------------------+---------+----------+----------+--------------+
| 2012-03-27至2012-03-27 | 1 | 2 | 2 | 105.99000 |
| 2012-03-27至2012-03-27 | 3 | 3 | 3 | 89.00000 |
| 2012-03-27至2012-03-27 | 5 | 1 | 1 | 36.00000 |
| 2012-03-27至2012-03-27 | 64 | 2 | 2 | 231.03000 |
+------------------------+---------+----------+----------+--------------+
4 rows in set (0.00 sec)mysql>