我有以下MySQL查询(显然timestamp
是在Unix时间):
SELECT usr_id, CONCAT(YEAR(FROM_UNIXTIME(timestamp)), "/", MONTH(FROM_UNIXTIME(timestamp)), "/", DAY(FROM_UNIXTIME(timestamp))) as date_stamp
FROM table
ORDER BY YEAR(FROM_UNIXTIME(timestamp)), MONTH(FROM_UNIXTIME(timestamp)), DAY(FROM_UNIXTIME(timestamp));
这会产生如下所示的内容:
$arr = array(
array('usr_id'=>3, 'date_stamp'=>'2011/6/6'),
array('usr_id'=>2, 'date_stamp'=>'2011/6/20'),
array('usr_id'=>2, 'date_stamp'=>'2011/6/20'), // same id and date as above
array('usr_id'=>5, 'date_stamp'=>'2011/6/20'), // same date as above
array('usr_id'=>1, 'date_stamp'=>'2011/6/21'),
array('usr_id'=>4, 'date_stamp'=>'2011/6/21'), // same date as above
array('usr_id'=>2, 'date_stamp'=>'2011/6/21'), // same date as above...
//... and same id as a day before
);
我想把它变成这样的东西:
$arr = array(
array('sum'=>1, 'date_stamp'=>'2011/6/6'),
array('sum'=>3, 'date_stamp'=>'2011/6/20'), // +2 as one of the 3...
//... for this date was a duplicate
array('sum'=>5, 'date_stamp'=>'2011/6/21'), // +2 as one of the 3...
//... was already there on a different day
);
这是我尝试过的,但我后来才意识到它只处理给定日期的唯一性,而不是我想要做的整个数组:
$sum = 0;
$tempRes = array();
$result = array();
$date = null;
foreach($arr as $row)
{
$date = $row['date_stamp'];
if (!in_array($row['usr_id'], $tempRes))
$tempRes[$date][] = $row2['usr_id'];
}
foreach ($tempRes as $date2 => $ids)
{
$sum += count($ids);
$result[] = array($date2, $sum);
}
基本上目的是产生每天usr_id
数的累积和,并确保在整个数组中只将相同的usr_id
计为一个,即按天排序的唯一usr_id
的累积总和。
如果您有更好地优化MySQL查询的想法,也欢迎。
编辑:我希望"累积"发生在整个数组上,而不仅仅是每天,就像我的示例输出一样,即第 1 天是 1,第 2 天是 3 (1+2),第 3 天是 5 (3+2)...等。
您可以先按天对唯一身份用户进行分组,如下所示:
foreach ($arr as $item) {
$days[$item['date_stamp']][$item['usr_id']] = 1; // value is irrelevant
}
然后,您可以创建一个所有用户的数组,将每天的用户合并到该数组上,并计算其结果以获得累积总和。
$all_users = array();
foreach ($days as $day => $users) {
$all_users = $all_users + $users;
$result[] = array('sum' => count($all_users), 'date_stamp' => $day);
}
我会在SQL中这样做。
类似的东西 - 这是伪代码,当然不是真正的SQL,因为我是MSSQL开发人员,但想法是有意义的。
这将首先选择所有唯一的用户 ID 及其日期。然后它将按日期对它们进行分组。在此之后,只需在 PHP 中运行它并加起来。
SELECT
COUNT(usr_id)
date_stamp
ts
FROM
(
SELECT
DISTINCT usr_id,
timestamp as ts,
CONCAT(YEAR(FROM_UNIXTIME(timestamp)), "/", MONTH(FROM_UNIXTIME(timestamp)), "/", DAY(FROM_UNIXTIME(timestamp))) as date_stamp
FROM
table
)
GROUP BY
date_stamp
ORDER BY
ts