duandi4815 2017-03-23 05:28
浏览 231
已采纳

通过PHP(多维数组)合并非常复杂的SQL数据结果

Sorry, I have a hard time to write proper thread title for this problem. Here's my question:

In short: How to merge array item and calculate.

Here's the full explanation

1) I have a (WP custom) database to track statistics: visits, unique visits, etc, and it's stored per post per date.

To make it easier to understand. Here's the screenshot of the table:

Stats MySQL Table

2) This is the example data when I queried it:

https://gist.github.com/turtlepod/8e7dc93bae7f0b665fd5aea8a9694998

So in this example we have multiple post ID: "90", "121", & "231"

We have multiple date in db: "2017-03-20", "2017-03-21", "2017-03-22"

We have multiple stats: "visits", and "unique_visits"

We also have a "stat_value" for each item.

Each item have unique ID.

All data is dynamically created when an event happen. so not all post_id have 2 stats or the above date.

Note: keep in mind that in real code, we have a lot more data and variations than the example above.

3) I need to merge the data:

The post_id "121" is the same as post "231", so we need to merge and add the "stat_value" into one data and remove "231" entry.

What is the best way to do this (dynamically) via PHP ?

I have this data:

$raw_data = array( ... ); // the one in github gist
$post_groups = array(
   '121' => array( '121', '231' ), // main post_id => array of alias.
);

It need to return the same data format as $raw_data, but remove the data of "231" and include/sum the "stat_value" of "231" to "121".

Thank you.

  • 写回答

1条回答 默认 最新

  • doubang4881 2017-03-23 15:37
    关注

    Try it with this:

    function david_transform_data($data, $groups) {
      if (empty($groups) === true) {
        return $data;
      }
    
      // Transform groups into a more useful format
      $transformed_groups = array();
      foreach ($groups as $post_id => $aliases) {
        foreach ($aliases as $alias) {
          if (absint($post_id) === absint($alias)) {
            continue;
          }
    
          $transformed_groups[absint($alias)] = $post_id;
        }
      }
    
      // Replace aliases with the real post id
      foreach ($data as $index => $stat) {
        if (isset($transformed_groups[absint($stat->post_id)]) === false) {
          continue;
        }
    
        $data[$index]->post_id = $transformed_groups[absint($stat->post_id)];
      }
    
      // Go through stats and merge those with the same post_id, stat_id
      // and stat_date
      $merged_stats = array();
      $index_tracker = 0;
      $stats_hash = array();
    
      foreach ($data as $index => $stat) {
        $hash_key = sprintf(
          '%s-%s-%s',
          $stat->post_id,
          $stat->stat_id,
          $stat->stat_date
        );
        if (isset($stats_hash[$hash_key]) === true) {
          $merged_stats[$stats_hash[$hash_key]]->stat_value += absint($stat->stat_value);
          continue;
        }
    
        $merged_stats[] = $stat;
        $stats_hash[$hash_key] = $index_tracker;
        $index_tracker++;
      }
    
      return $merged_stats;
    }
    
    var_dump(david_transform_data($raw_data, $post_groups));
    

    There might be a faster solution but this is the first thing that came to my mind.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名