One SQL query or many in a loop?

Question

I need to output several rows from a table and process them in two ways:

• aggregated by key
• line by line sorted by the same key

The table looks something like this:

table (
   key,
   string_data,
   numeric_data
)

So, I am considering two approaches to the function that I am writing.

The first one will pull out aggregated data with one query, and then query again inside the loop for each data set row by row (the following pseudo-type PHP type):

$rows = query(
        "SELECT key,SUM(numeric_data)
         FROM table
         GROUP BY key"
    );

foreach ($rows as $row) {
    <process aggregate data in $row>

    $key = $row['key'];
    $row_by_row_data = handle_individual_rows($key);
}

function handle_individual_rows($key)
{
    $rows = query(
            "SELECT string_data
             FROM table WHERE key=?",
            $key
        );

    <process $rows one row at a time>

    return $processed_data;
}

Or, I could make one big request and let the code do all the work:

$rows = query(
    "SELECT key, string_data, numeric_data
     FROM table"
);

foreach ($rows as $row) {
    <process rows individually and calculate aggregates as I go>
}

Performance is not a practical issue in this application; I just want to write reasonable and convenient code.

, - , . , , ?

Answer 1

SQL , .

• GROUP BY

, : .

, SUM:

SELECT  string_data, numeric_data, SUM(numeric_data) OVER (PARTITION BY key)
FROM    table

, , , SUM key, PHP.

MySQL:

SELECT  key, string_data, numeric_data,
        (
        SELECT  SUM(numeric_data)
        FROM    table ti
        WHERE   ti.key = to.key
        ) AS key_sum
FROM    table to

Answer 2

, . .

, " , " .:)

Answer 3

, . , .

, , , , ?

Answer 4

, , SQL-. " " , SQL PHP.

- , , SQL?

Answer 5

, , . , , , , , .

, , .

SQL .

Answer 6

sql, , . .

Answer 7

, - , , : .

• , , , ,

• perf , ,

• , , , , ,

• , ,

• , , - .

.

, , , db - , ...

Answer 8

, . , . , , . , . SQL- . ?