php - 如何加快我的php PSQL查询

请求的表定义

                Table "public.call_record"
     Column      |          Type          |   Modifiers
-----------------+------------------------+---------------
 cntrct_id       | character varying(15)  | not null
 call_regard     | text                   |
 port_type       | character varying(9)   |
 inst            | text                   |
 info_taken      | character varying(40)  |
 log_date        | date                   | not null
 log_time        | time without time zone | not null
 act_taken       | text                   |
 use_material    | text                   |
 targ_pest       | integer                |
 work_comp_by    | text                   |
 emp_no          | integer                |
 comp_date       | date                   |
 job_start_time  | time without time zone |
 job_leave_time  | time without time zone |
 comp_val        | boolean                | default false
 fti_call_regd   | public.tsvector        |
 fti_inst        | public.tsvector        |
 fti_act_take    | public.tsvector        |
 route           | character(3)           |
 act_port        | text                   |
 targ_pest_opt   | text                   |
 call_regard_opt | text                   |
 targpest_other  | text                   |
 date_sched      | date                   |
 custord_num     | integer                |
 dist_id         | integer                |
 phone_slot      | integer                | default 0
 Indexes:
    "call_record_pkey" PRIMARY KEY, btree (cntrct_id, log_date, log_time)
    "route_index" hash (route)
 Check constraints:
    "call_record_targ_pest_check" CHECK (targ_pest <= 100)
    "call_record_targ_pest_check1" CHECK (targ_pest >= 0)

          Table "public.per_call"
 Column  |         Type         | Modifiers
---------+----------------------+-----------
 dist_id | character varying(2) |
 route   | character varying(2) |
 type    | character(1)         |
 total   | integer              |

我需要从两个表中获取数据并在一个报表中打印出来。报告应该是这样的：

district | route | type | total | callbacks
         | 01    | T    | 12    | 5
         | 02    | P    | 0     | 0
         | 03    | P    | 3     | 1
2        | 01    | T    | 4     | 1
         | 02    | T    | 1     | 0
         | 03    | P    | 0     | 0
etc... (this is theoretical sample data)

所以，本质上，我需要从每次调用的表中获取dist_id、route、type和count（*）
以及从表call_记录返回的call_计数
问题：在表格中循环会使它变得非常缓慢。如何调整以下PSQL查询，以便不必循环并正确地回显表格数据？
如果有什么不清楚的地方请告诉我，我会尽力澄清

   echo    '<table align="center" border = 2>
            <th>DISTRICT</th>
            <th>ROUTE</th>
            <th>TYPE</th>
            <th>TOTAL</th>
            <th>CALL BACKS</th>';


    $SQL = " SELECT per_call.dist_id, per_call.route, per_call.type, per_call.total
            FROM per_call, call_record
            WHERE TRUE  ";

    if($type == 'termite'){
            $SQL = $SQL." AND per_call.type  = 'T' ";
    }
    else{
            $SQL = $SQL." AND per_call.type = 'P' ";
    }
    $SQL = $SQL."   AND call_record.dist_id = per_call.dist_id
                    AND call_record.log_date >= '$startDate'
                    AND call_record.log_date <= '$endDate'
                    ORDER BY per_call.dist_id, per_call.route, per_call.type    ASC ";

    echo    $SQL;
                    /*AND call_record.log_date = '$startDate'
                    AND call_record.log_date = '$endDate'*/

    $Q = pg_query($connect,$SQL);
    while($row = pg_fetch_row($Q)){
            $dist = $row[0];
            $route = $row[1];
            $type = $row[2];
            $total = $row[3];

            echo '<tr>';
            echo '<td align="center">'.$dist.'</td>';
            echo '<td align="center">'.$route.'</td>';
            echo '<td align="center">'.$type.'</td>';
            echo '<td align="center">'.$total.'</td>';

            $SQL2 = "SELECT COUNT(*)
                    FROM call_record
                    WHERE dist_id = $dist
                    AND route = '$route'
                    AND substring(cntrct_id from 2 for 1) = '$type'
                    AND substring(call_regard_opt from 2 for 1) = '1'

                     ";
            $Q2 = pg_query($connect,$SQL2);
            $row2 = pg_fetch_row($Q2);

            $callbacks = $row2[0];

            echo '<td align="center">'.$callbacks.'</td>';

            echo '</tr>';
    }

    echo "</table>";

最佳答案

select pc.dist_id, pc.route, pc.type, pc.total,
    count(
        substring(cntrct_id from 2 for 1) = '$type'
        AND substring(call_regard_opt from 2 for 1) = '1'
        or null
    ) callbacks
from
    per_call pc
    inner join
    call_record cr on cr.dist_id = pc.dist_id
where cr.log_date between '$startdate' and cr.log_date <= '$enddate'
group by pc.dist_id, pc.route, pc.type, pc.total
order by pc.dist_id, pc.route, pc.type asc

关于php - 如何加快我的php PSQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13937439/