php - 如何从表记录和分数中获取X连续天数的间隔和值

Question

我试图从两个表中获取 X 个连续日期(间隔)的平均分数。我的意思是，根据列 Records.status 的值，日期必须是连续的(仅当状态为 T 或 P 时，才会选择行值，尤其是 Scores.score)。

例如，如果我为 personid = 133* 选择 4 个连续日期的间隔，我喜欢返回以下内容(预平均计算，我认为我应该通过 SQL 查询得到它？)

2015-07-11  5
2015-10-17  2
2015-11-06  5
2016-01-20  5

2016-01-30  4
2016-05-19  4
2016–09-07  1   
2016-09-28  3

2016-12-29  2
2017-01-17  1
2017-01-22  3
2017-04-02  2

并绘制图表(平均计算后，我认为我需要使用 PHP 来完成)

group 1 (2015-07-11 / 2016-01-20) 4.25
group 2 (2016-01-30 / 2016-09-28) 3.00
group 3 (2016-12-29 / 2017-04-02) 2.00

*这是我随机生成并正在测试的一些示例数据，但我的实际数据更大，并且结构更好，具有更多列和真正连续的日期(周一至周五，彼此紧随其后的几天)。

http://sqlfiddle.com/#!9/4b7a62/1

非常欢迎任何提示、建议。

MySQL版本:5.6.26[edit1] 不知何故，我的 sqlfiddle 片段处于离线状态，但这应该是我的示例设置

————2 DB tables schema’s
CREATE TABLE IF NOT EXISTS `records` (
  `person` varchar(32) NOT NULL,
  `status` varchar(32) NOT NULL,
  `purdate` date NOT NULL,
  `personid` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL
)

CREATE TABLE IF NOT EXISTS `scores` (
  `personid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `id` int(11) NOT NULL
) 

—-php for sample data—
function getRandomDateTime($startDate, $endDate, $num) {

    for ($i = 0; $i < $num; $i++) {
        $dateArr[] = date('Y-m-d', mt_rand(strtotime($startDate), strtotime($endDate)));       
    }
    sort($dateArr, SORTDATE);// SORT_REGULAR);SORTDATE);//
    return $dateArr;

}
$test = getRandomDateTime('2015-06-03', '2017-05-12', 100);

echo "insert into records (person, status, purdate, personID) values\r\n";
foreach($test as $value) {
    $arrCode = ['P','L','T'];
    $arrId = [133, 145,156];
   $rand = $arrCode[array_rand($arrCode, 1)];
   $randID = $arrId[array_rand($arrId, 1)];
    echo "('person_name', '".$rand."', '".$value."', '".$randID."'),\r\n";
}

echo "insert into scores (personID, score) values\r\n";
for ($i=0;$i < 100;$i++) {
    $arrId = [133, 145,156];
    $randID = $arrId[array_rand($arrId, 1)];
    echo "('".$randID."','".rand(1,5)."'),\r\n";

}

——— SQL Query To Update The Date Column—
UPDATE scores  
SET scores.date = (  
SELECT records.purdate  
    FROM records  
    WHERE records.id = scores.id  
);

[edit2] 这个简单的 php 函数，我称之为:。getConsecutiveInterval(4)。

  function getConsecutiveInterval($interval) {
    global $conn;

//    $interval = 4;
    $offset = '';
// For loop will control the results sets divided by 4 
    for ($i = 1; $i <= $interval; $i++) {
        // To add the offset after the first set
        if ($offset > 0) {
            $limitValues = $interval . ", " . $offset . " ";
        } else {
            $limitValues = $interval;
        }
// Query is the same and at the end of it you include LIMIT to be controlled by the loop.
        $q = "SELECT a.purdate, b.score, a.status "
                . "FROM records a "
                . "INNER JOIN scores2 b "
                . "ON a.purdate = b.date AND a.personid = b.personid "
                . "WHERE a.personid = 133 AND a.status IN('P','T') "
                . "ORDER BY purdate ASC, score DESC ";
        $sqlquery = $q . "  LIMIT " . $limitValues;
        $avg = 0;
        $total = 0;
    //Total Found Use To Divide by ... For Max Loop
    $result = mysqli_query($conn, $q);
    $num_rows = mysqli_num_rows($result);
    //end
        foreach (mysqli_query($conn, $sqlquery) as $results) {
            // Do Something
            $total += $results['score'];
            $avg = $total / $interval;
        }
        echo $avg . '<br/>';
        $offset += $interval;
    } echo '<hr/>';
}

我知道随机数据输出不同的平均值，但基于我的以下随机数据和硬编码的 personid = 133

我使用 PHP 函数得到的平均输出

我预计平均值为 2.75、3.5 和 3.5(基于其余 2 个日期而不是 4 个)

当我使用 getConsecutiveInterval(3); 时我预计平均值为 3.33、3.33、2.66 和 4(基于 1 个日期)

Answer 1

更新:我之前给您提供的示例帮助我了解您的需求和您的背景知识(您更喜欢在哪些方面进行开发)。

我知道 PHP 解决方案最适合您，但您知道并非所有 MySQL 解决方案都应该依赖 PHP。所以，我决定采用我能想到的最好的方法。

我已经获得了您通过 PHP 提供的示例，它们足以更好地理解您正在使用的数据类型。

从这些示例中我看到records.purdate和scores.date是相同的，您基本上将purdate列复制到scores.date列中。这可能是多余的，但它将帮助我们获取每个连续日期的开始日期和结束日期。

首先我需要提及的是，我正在开发 MySQL v5.7，并且使用 MySQL Workbench 6.3作为 IDE(自从我使用 phpMyAdmin 以来已经很长时间了，但它也应该可以使用它)。

您需要创建一个存储过程，如果您不知道如何在 phpMyAdmin 中管理它，只需 google 一下即可。

我将为您提供一个可用的(已测试):

CREATE PROCEDURE `getConsecutiveInterval`(IN `selectRows` INT, IN `skippedRows` INT)
BEGIN
SET @selectRows = selectRows; 
SET @skippedRows = skippedRows; 

IF skippedRows = 0 THEN
SET @skippedRows = "";
ELSE 
SET @skippedRows = CONCAT(" , " , skippedRows);
END IF;

SET @q = CONCAT("SELECT concat(date_format(MIN(StartDate), '%Y-%m-%d'), '  /  ', date_format(MAX(EndDate), '%Y-%m-%d')) AS Dates, AVG(Score)
FROM (
SELECT 
    a.purdate AS StartDate, 
    b.date AS EndDate, 
    b.score  AS Score
FROM records a 
LEFT JOIN scores b 
ON a.purdate = b.date AND a.personid = b.personid 
WHERE 
    a.personid = 133
AND a.status IN('P','T') 
AND b.score IS NOT NULL
ORDER BY purdate ASC, score DESC 
LIMIT ", @selectRows, @skippedRows, " ", ") D;");

PREPARE ConsecutiveInterval FROM @q;
EXECUTE ConsecutiveInterval;
DEALLOCATE PREPARE ConsecutiveInterval;
END

此存储过程与 getConsecutiveInterval() 函数类似，只不过它是在 MySQL 中。

它是如何工作的:您可以通过

调用存储过程

CALL getConsecutiveInterval(selectRows,skippedRows)

我在存储过程中做了一个条件，如果skippedRows为0，那么它将是一个空字符串。否则，将始终返回跳过的行。

例如，使用您提供的示例:

CALL getConsecutiveInterval(4,0)

将返回:

'2015-07-11  /  2016-01-20', '4.25'

和

CALL getConsecutiveInterval(4,1)

会回来

2016-01-30  /  2016-01-30   4.00

等等。

selectRows var 是 PHP 中的 $interval，skippedRows 是 $offset。

然后，从 PHP 端，您可以通过以下方式调用它:

$query = "CALL getConsecutiveInterval( " . $interval . " , "  . $offset .")";

这样，您只需通过 PHP 的输出来控制 $interval 和 $offset 整数，其余部分将由 MySQL 本身维护。

$offset 的计算将与之前相同:

$offset += $interval;

您还可以更改存储过程以使用更多参数进行扩展，例如 personid、status ..等。无论您需要什么参数，您都可以随时扩展它。

例如，我将用 personid 扩展它:

CREATE PROCEDURE `getConsecutiveInterval`(IN `selectRows` INT, IN `skippedRows` INT, IN personID INT)
BEGIN
SET @selectRows = selectRows; 
SET @skippedRows = skippedRows; 
SET @personid = personID;

IF skippedRows = 0 THEN
SET @skippedRows = "";
ELSE 
SET @skippedRows = CONCAT(" , " , skippedRows);
END IF;

IF personID > 0 THEN 
SET @personid = CONCAT(" AND a.personid = ",  personID); 
ELSE 
SET @personid = ""; 
END IF;

SET @q = CONCAT("SELECT concat(date_format(MIN(StartDate), '%Y-%m-%d'), '  /  ', date_format(MAX(EndDate), '%Y-%m-%d')) AS Dates, AVG(Score)
FROM (
SELECT 
    a.purdate AS StartDate, 
    b.date AS EndDate, 
    b.score  AS Score
FROM records a 
LEFT JOIN scores b 
ON a.purdate = b.date AND a.personid = b.personid 
WHERE 
    a.status IN('P','T') 
AND b.score IS NOT NULL ", @personid, " ORDER BY purdate ASC, score DESC LIMIT ", @selectRows, @skippedRows, " ", ") D;");

PREPARE ConsecutiveInterval FROM @q;
EXECUTE ConsecutiveInterval;
DEALLOCATE PREPARE ConsecutiveInterval;
END

这将添加另一个要调用的参数，如下所示:

CALL getConsecutiveInterval(4,0, 133);

133是personid，如果我把它改成0，那么条件a.personid = 133

将从查询中删除，我将根据表排序获得随机数据。

我希望此更新能够对您的旅程有所帮助。