php - 插入数据作为时间点，检索数据作为时间范围

Question

环境

PHP:5.6
MySQL:5.5 (MariaDB)

数据

我从一个管理网站获得以下数据作为数组结构:

[
    0 => [
        0 => ['factor' => 1.25], // hour from 0:00 to 0:59
        1 => ['factor' => 1.25], // hour from 1:00 to 1:59
        2 => ['factor' => 1.25], // hour from 2:00 to 2:59
        .
        .
        .
        23 => ['factor' => 1.25]
    ], // monday
    1 => [
        0 => ['factor' => 1.0], // hour from 0:00 to 0:59
        1 => ['factor' => 1.0], // hour from 1:00 to 1:59
        2 => ['factor' => 1.0], // hour from 2:00 to 2:59
        .
        .
        .
        23 => ['factor' => 1.0]
    ], // tuesday
    .
    .
    .
    6 => [
        0 => ['factor' => 1.0], // hour from 0:00 to 0:59
        1 => ['factor' => 1.0], // hour from 1:00 to 1:59
        2 => ['factor' => 1.0], // hour from 2:00 to 2:59
        .
        .
        .
        23 => ['factor' => 1.0]
    ] // sunday
]

数据表示客户必须为服务支付额外费用(“因素”)的日期和时间。

将此数据插入表格不是问题，它可能类似于下表(其中“#”只是行的数字，“some_fk”是与客户的联系，但与此处无关):

| #  | day | hour | factor | some_fk |  |----|-----|------|--------|---------|  | 1  | 0   | 0    | 1.25   |         |  |----|-----|------|--------|---------|  | 2  | 0   | 1    | 1.25   |         |  |----|-----|------|--------|---------|  | 3  | 0   | 2    | 1.25   |         |  |----|-----|------|--------|---------|  | 4  | 0   | 3    | 1.25   |         |  |----|-----|------|--------|---------|  | 5  | 0   | 4    | 1.25   |         |  |----|-----|------|--------|---------|  | 6  | 0   | 5    | 1.25   |         |  |----|-----|------|--------|---------|  | 7  | 0   | 6    | 1.0    |         |  |----|-----|------|--------|---------|  | 8  | 0   | 7    | 1.0    |         |  |----|-----|------|--------|---------|  | 9  | 0   | 8    | 1.0    |         |  |----|-----|------|--------|---------|  | 10 | 0   | 9    | 1.0    |         |  .  .  .  |----|-----|------|--------|---------|  | 23 | 0   | 22   | 1.0    |         |  |----|-----|------|--------|---------|  | 24 | 0   | 23   | 1.0    |         |  |----|-----|------|--------|---------|  | 25 | 1   | 0    | 1.0    |         |  |----|-----|------|--------|---------|  | 26 | 1   | 1    | 1.5    |         |  |----|-----|------|--------|---------|  | 27 | 1   | 2    | 1.5    |         |  .  .  .  |----|-----|------|--------|---------|  | 37 | 1   | 12   | 1.5    |         |  |----|-----|------|--------|---------|  | 38 | 1   | 13   | 1.5    |         |  |----|-----|------|--------|---------|  | 39 | 1   | 14   | 1.25   |         |  |----|-----|------|--------|---------|  | 40 | 1   | 15   | 1.25   |         |  |----|-----|------|--------|---------|  | 41 | 1   | 16   | 1.25   |         |  |----|-----|------|--------|---------|  | 42 | 1   | 2    | 1.25   |         |  .  .  .  

挑战/问题

这种“管理”形式的插入和检索(数组输入和数组输出)不会经常发生，所以这不是主要问题，但它声称是数据结构。

大多数时候我需要一个时间范围内的数据(只检索，不写入)，其中“因素”列表示范围的开始和结束。即(来自上表):

• 范围 1:因子 == 1.25；第 1 - 6 行；天=0,小时=0 - 天=0,小时=5
• 范围 2:因子 == 1.0；第 7 - 25 行；天=0，小时=6 - 天=1，小时=0
• 范围 3:因子 == 1.5；第 26 - 38 行；天=1,小时=1 - 天=1,小时=13
• 范围 4:因子 == 1.25；第 39 行——……；天=1,小时=14 - 天=...,小时=...

也许获取范围内的数据太具有挑战性(目前我无法想象一种数据格式，以后可以在 PHP 中使用)，所以每一行的因子都与前一行不同行，可以做到这一点:

• 因子 == 1.25；第 1 行
• 因子 == 1.0；第 7 行
• 因子 == 1.5；第 26 行
• 因子 == 1.25；第 39 行

问题

1. 有没有一种方法可以将数据作为“范围”(或因子发生变化的行)作为 SQL 查询从表和数据结构(上面给出)中获取？
   • 如果没有，我可以更改表/数据结构以使用 SQL 获取数据吗？
2. 如果根本没有“SQL 方式”，推荐的方式是什么:
   • 插入上面给出的数据并以编程方式处理每个请求的范围？
     -或者-
   • 考虑一种不同的数据结构，在保存之前处理范围并通过 SQL 检索范围？

Answer 1

在思考和测试了一些可能的解决方案之后，如果有人遇到类似问题，我将发布我最终解决它的方法:

因为 SQL 解决方案不是那么简单(甚至不可能)，所以我考虑了我的第二个问题，结合只在数据库中保存因子随日期和时间的变化。

reduceServiceTimes() 函数的参数是问题中的数组。它返回一个具有以下结构的数组:

[
    ['day' => 0, 'hour' => 0,'factor' => 1.25],
    ['day' => 0, 'hour' => 6,'factor' => 1.0],
    ['day' => 1, 'hour' => 1,'factor' => 1.5],
    ['day' => 1, 'hour' => 14,'factor' => 1.25],
    .
    .
    .
]

函数 fillServiceTimes() 采用与参数具有相同结构的数组，并返回问题中的数组。

从管理网站获取数据时，我在 PHP 中使用以下函数将完整的数据集缩减为仅“时间”上的变化点:

function reduceServiceTimes($serviceTimes) {

    // prepare current factor
    $currentFactor = null;

    // prepare reduced service times
    $reducedServiceTimes = [];

    // walk through days
    for($day = 0; $day <= 6; $day++) {

        // walk through hours
        for($hour = 0; $hour <= 23; $hour++) {

            // check if first entry
            if($day == 0 && $hour == 0) {

                // add first entry
                $reducedServiceTimes[] = [
                    'day' => 0,
                    'hour' => 0,
                    'factor' => $serviceTimes[0]['hours'][0]['factor']
                ];

                // remember current factor
                $currentFactor = $serviceTimes[0]['hours'][0]['factor'];
            } else {

                // check last factor
                if($serviceTimes[$day]['hours'][$hour]['factor'] != $currentFactor) {

                    // first entry
                    $reducedServiceTimes[] = [
                        'day' => $day,
                        'hour' => $hour,
                        'factor' => $serviceTimes[$day]['hours'][$hour]['factor']
                    ];

                    // remember current factor
                    $currentFactor = $serviceTimes[$day]['hours'][$hour]['factor'];
                }
            }
        }
    }

    // return
    return $reducedServiceTimes;
}

返回的数组可以很容易地插入到我的问题中提到的数据库结构中。

为了为网站取回完全填充的数组，我使用以下函数用相同的因子填充“时间”变化点之间的间隙:

function fillServiceTimes($rows) {

    // prepare complete list
    $completeServiceTimes = [
        0 => [],
        1 => [],
        2 => [],
        3 => [],
        4 => [],
        5 => [],
        6 => [],
    ];

    // walk through rows
    for($i = 0; $i < count($rows); $i++) {

        // simplify days and hours
        $day = $rows[$i]['day'];
        $nextDay = ($i == count($rows) - 1 ? 6 : $rows[$i + 1]['day']);
        $hour = $rows[$i]['hour'];
        $nextHour = ($i == count($rows) - 1 ? 24 : $rows[$i + 1]['hour']);
        $value = ['factor' => floatval($rows[$i]['factor'])];

        // check if start and end are the same day
        if(($day == $nextDay) || ($nextDay == $day + 1 && $nextHour == 0)) {

            // check if next hour is 0
            if($nextHour == 0) {
                $nextHour = 24;
            }
            $completeServiceTimes[$day] += array_fill($hour, $nextHour - $hour, $value);
        } else {

            // walk from day to next day
            for($currDay = $day; $currDay <= $nextDay; $currDay++) {

                // check if first day and no complete day
                if($currDay == $day && $hour > 0) {
                    $completeServiceTimes[$currDay] += array_fill($hour, 24 - $hour, $value);
                // check if last day and no complete day
                } elseif($currDay == $nextDay && $nextHour <= 23) {
                    $completeServiceTimes[$currDay] += array_fill(0, $nextHour, $value);
                // any other complete day
                } else {
                    $completeServiceTimes[$currDay] += array_fill(0, 24, $value);
                }
            }
        }
    }

    // return
    return $completeServiceTimes;
}

希望这可以帮助其他人......