我如何创建执行与我的php函数相似的过程的程序(优化加速) [英] How do I create procedure which does similar job of my php function (Optimization speedup)

问题描述

我有如下表

mysql> select * from dts;
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 |
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 |
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 |
|  5 |    1 |    2 |      5 |    0 |    0 |    0 |    2 |    5 |
|  6 |    1 |    2 |      6 |    0 |    0 |    0 |    0 |    1 |
|  7 |    1 |    2 |      7 |    0 |    1 |    0 |    0 |    0 |
|  8 |    2 |    2 |      1 |    1 |    1 |    1 |    1 |    2 |
|  9 |    2 |    2 |      2 |    0 |    0 |    0 |    0 |    0 |
| 10 |    3 |    2 |      3 |    0 |    0 |    0 |    0 |    0 |
| 11 |    3 |    3 |      1 |    1 |    1 |    0 |    0 |    1 |
| 12 |    3 |    3 |      5 |    0 |    0 |    1 |    1 |    0 |
+----+------+------+--------+------+------+------+------+------+
12 rows in set (0.00 sec)

我想将key1，key2，db，table和以逗号分隔的字段列表传递给过程，并且想从那些字段中搜索select语句返回的记录的非零值(如果假设的话)非零的字段会中断循环并返回字符串.

I would like to pass key1,key2,db,table and list of fields comma separated to procedure, and would like to search for nonzero values from those fields for the records returned by the select statement, if suppose If I get all fields which are nonzero just break loop and return string back .

我在下面尝试使用php

What I tried is below using php

function show_available($key1, $key2, $db, $table, $conn, $fields=null) 
{

/* Select all fields in argument from db table where key... */
$query = "select ".$fields." FROM $db.$table where key1=$key1 and key2=$key2";

/* Query */
$result = $conn->query($query , MYSQLI_USE_RESULT);

/* Output array  */
$out = array();
while($row=$result->fetch_assoc())
{
    /* Loop through fields */
    foreach($row as $key => $val)
    {
        /* If val is greater than 0*/
        if($val > 0 ){

            /*Ok we got field which has value greater than 0*/
            $out[$key]=1;   
        }       
    }
    /* If all fields are found ok in so many records where key1=x and key2=x, break loop */
    if(count($out) == count($field_arr))break;
}

/* Return which all fields has value greater than 0 */
return implode(',', array_keys($out));

 }

在同一个函数中，我想转换为过程以加快我的任务的速度，并希望获得如下所示的输出，它是如何可能的，请帮助某人

In the same function, I would like to convert into procedure to speedup my task and want to have output like below, how it is possible, please help someone

如果我通过someprocedure(1,1,db,table,'pr1,pr2,pr3,pr4,pr5')，我想获得输出pr1,pr3,pr4,pr5，因为当key1=1和key2=1

If I pass someprocedure(1,1,db,table,'pr1,pr2,pr3,pr4,pr5') I would like to get output pr1,pr3,pr4,pr5 because when key1=1 and key2=1

+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 | - Found pr3,pr5
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 | - Found pr4
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 | - Found pr1

类似地，对于key1 = 2和key2 = 2

Similarly for key1=2 and key2=2

|  8 |    2 |    2 |      1 |    1 |    1 |    1 |    1 |    2 | - Found pr1-pr5, break loop and return string
|  9 |    2 |    2 |      2 |    0 |    0 |    0 |    0 |    0 |

预期产量

# For procedure call expected o/p
key1       key2 fields_non_zero
1           1   pr1,pr3,pr4,pr5

1           2   pr2,pr4,pr5

2           2   pr1,pr2,pr3,pr4,pr5

3           2

3           3   pr1,pr2,pr3,pr4,pr5

表转储

DROP TABLE IF EXISTS `dts`;
CREATE TABLE `dts` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` int(11) DEFAULT '-99',
  `key2` int(11) DEFAULT '-99',
  `serial` int(11) DEFAULT '-99',
  `pr1` int(11) DEFAULT '-99',
  `pr2` int(11) DEFAULT '-99',
  `pr3` int(11) DEFAULT '-99',
  `pr4` int(11) DEFAULT '-99',
  `pr5` int(11) DEFAULT '-99',
  PRIMARY KEY (`Id`),
  KEY `main` (`key1`,`key2`,`serial`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


LOCK TABLES `dts` WRITE;
INSERT INTO `dts` VALUES (1,1,1,1,0,0,1,0,2),(2,1,1,2,0,0,0,0,0),(3,1,1,3,0,0,0,1,0),(4,1,1,4,1,0,1,1,3),(5,1,2,5,0,0,0,2,5),(6,1,2,6,0,0,0,0,1),(7,1,2,7,0,1,0,0,0),(8,2,2,1,1,1,1,1,2),(9,2,2,2,0,0,0,0,0),(10,3,2,3,0,0,0,0,0),(11,3,3,1,1,1,0,0,1),(12,3,3,5,0,0,1,1,0);
UNLOCK TABLES;

推荐答案

您需要的是使用max(或sum)和concat_ws进行聚合:

What you need is aggregation with max (or sum) with concat_ws:

select
    key1, key2, 
    concat_ws(
        ',',
        case when max(pr1) <> 0 then 'pr1' end,
        case when max(pr2) <> 0 then 'pr2' end,
        case when max(pr3) <> 0 then 'pr3' end,
        case when max(pr4) <> 0 then 'pr4' end,
        case when max(pr5) <> 0 then 'pr5' end
    ) as val
from dts
group by key1, key2;

产生:

key1    key2    val
1       1       pr1,pr3,pr4,pr5
1       2       pr2,pr4,pr5
2       2       pr1,pr2,pr3,pr4,pr5
3       2       
3       3       pr1,pr2,pr3,pr4,pr5

这篇关于我如何创建执行与我的php函数相似的过程的程序(优化加速)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！