php - 在 PHP 中按天获取总销售额

Question

我以前从来没有这样做过，所以我在这里迷路了。

我有以下交易表:

transaction_id  transaction_user    transaction_ammount transaction_time
1                     2                   950              1318482908
2                     2                   750              1318482916
3                     2                   950              1318482939

然后，我需要创建一个查询，帮助我提取每周数据，按天分组，这样我就可以创建一个图表，明白了吗？

到目前为止，我已经在 PHP 中检测到 周数 并打印完整的工作日(我使用的是 phpbb3):

    $format = 'l'; 
    $ts = time();   // set current Unix timestamp
    $today = date($format, $ts); // set today
    $year = date('o', $ts);
    $week = date('W', $ts);
    $weekdays = 7; 


    for($i = 1; $i <= $weekdays; $i++) 
    {
        // timestamp from ISO week date format
        $ts = strtotime($year.'W'.$week.$i);

        $day = date($format, $ts);
        // test if $day is $today
        if ($day == $today) {
            $day = '<strong>' . $day . '</strong>';
        }

        $template->assign_block_vars('time', array(
            'DATE'                  => $day,
        ));
    }

现在，我想安排发生在星期一、星期二...等...的总销售额

Answer 1

这个 mysql 查询

select week(transaction_time) as weekno, weekday(transaction_time) as dayno, sum(transaction_ammount) as total_amount
group by     week(transaction_time), weekday(transaction_time)

会给你这个结果集

|weekno|dayno|total_amount

这里有一些对 mysql 中这些函数的引用，因此您可以解释数据

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_weekday

更新，试试这个 sql，因为它可能需要转换时间戳以使其他函数正常工作

select week(FROM_UNIXTIME(transaction_time)) as weekno, weekday(FROM_UNIXTIME(transaction_time)) as dayno, sum(transaction_ammount) as total_amount
group by    week(FROM_UNIXTIME(transaction_time)), weekday(FROM_UNIXTIME(transaction_time))