Hi,

Well here is how Shard-Query does them. I assume you would do something very similarly internally with a temp table.

a) it create a temporary table for the query reserving null rows for the window functions

b) it adds to the temporary a unique id for each row of the resultset. This is used for framing.

c) it adds a hash of the order by columns for the window function for ordering

d) it adds a hash of the partition columns for partitioning

After the regular resultset is stored in the temp table, a function sweeps the table for

each window function, calculating the result of the function based on the framing clause,

then the column in the resultset is updated to reflect the computed value.

Finally, the resultset is returned to the client.

Here is the SQL log of the following query:

mysql> call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1); +-----------+-------+--------+------------------+

+-----------+-------+--------+------------------+

| develop | 7 | 4200 | 0.2 |

| develop | 9 | 4500 | 0.4 |

| develop | 11 | 5200 | 0.8 |

| develop | 10 | 5200 | 0.8 |

| develop | 8 | 6000 | 1 |

| sales | 3 | 4800 | 0.66666666666667 |

| sales | 4 | 4800 | 0.66666666666667 |

| sales | 1 | 5000 | 1 |

| personnel | 5 | 3500 | 0.5 |

| personnel | 2 | 3900 | 1 |

+-----------+-------+--------+------------------+

10 rows in set (0.08 sec)

Query OK, 0 rows affected (0.09 sec)

150303 13:12:05 1533 Query call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1)

-- get meta data for resultset (notice 0=1 in where clause)

1553 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash

FROM empsalary AS `empsalary` WHERE 1=1 LIMIT 0

-- create temp table

1553 Query CREATE TABLE aggregation_tmp_45403179 (wf_rownum bigint auto_increment primary key,expr$0 VARCHAR(255),expr$1 VARCHAR(255),expr$2 VARCHAR(255),wf0 VARCHAR(255),depname VARCHAR(255),wf0_hash VARCHAR(255),salary VARCHAR(255),wf0_obhash VARCHAR(255)) ENGINE=MYISAM

-- get resultset

1556 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash

FROM empsalary AS `empsalary` WHERE 1=1

-- store resultset

1557 Query INSERT INTO `aggregation_tmp_45403179` VALUES (NULL,'develop',11,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'develop',7,4200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4200,'c79fce75b1583ddd36a96178757e0d8d0ac91228'),(NULL,'develop',9,4500,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4500,'97a87b470fe9ed5ff51ff9b8543e937e6016d48c'),(NULL,'develop',8,6000,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',6000,'31d9ddeaa80bc88c1f3117b9724726ebcc7fc72d'),(NULL,'develop',10,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'personnel',5,3500,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3500,'65609286cc04ece831a844984a6bc9eb80450cf7'),(NULL,'personnel',2,3900,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3900,'5446569e8572251dcac168152d6c37074427eae3'),(NULL,'sales',3,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95'),(NULL,'sales',1,5000,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',5000,'f8237d8959e03355010bb85cc3dc46a46fb31110'),(NULL,'sales',4,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95')

-- handle window function 0 (the only one in this case)

-- get the hashes for each partition

1553 Query SELECT distinct wf0_hash h from aggregation_tmp_45403179 ORDER BY salary asc

-- compute the values for each partition (three in this case)

-- process the window and update the temp table (see code at the end for wf_cume) for each partition in turn.

-- as you can see there is a select followed by updates

1553 Query SELECT *,NULL as wf0

FROM `aggregation_tmp_45403179` where wf0_hash='fc08ce9ebee8734c2dc883c0dbd607686bdce8f3' ORDER BY salary asc

1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.5 WHERE wf_rownum in (6)

1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (7)

1553 Query SELECT *,NULL as wf0

FROM `aggregation_tmp_45403179` where wf0_hash='59248c4dae276a021cb296d2ee0e6a0c962a8d7f' ORDER BY salary asc

1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.66666666666667 WHERE wf_rownum in (8,10)

1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (9)

1553 Query SELECT *,NULL as wf0

FROM `aggregation_tmp_45403179` where wf0_hash='418a6bc4deccf0f7d5182192d51a54e504b3f3c9' ORDER BY salary asc

1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.2 WHERE wf_rownum in (2)

1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.4 WHERE wf_rownum in (3)

1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.8 WHERE wf_rownum in (1,5)

1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (4)

-- return resultset to client

1559 Query SELECT expr$0 AS `depname`,expr$1 AS `empno`,expr$2 AS `salary`,wf0 as `ss`

FROM `aggregation_tmp_45403179` ORDER BY wf0_hash,salary asc

-- remove temp table

1559 Query DROP TABLE IF EXISTS aggregation_tmp_45403179

1559 Quit

1550 Quit

150303 13:12:12 1533 Query set global general_log=0

protected function wf_cume_dist($num,$state) {

static $sum;

$win = $state->windows[$num];

if(empty($win['order'])) {

if($percent)

$sql = "update " . $state->table_name . " set wf{$num}=1";

else

$sql = "update " . $state->table_name . " set wf{$num}=0";

$state->DAL->my_query($sql);

if($err = $state->DAL->my_error()) {

$this->errors[] = $err;

return false;

}

return true;

} else {

/* running sum*/

$sql = "SELECT distinct wf{$num}_hash h from " . $state->table_name . " ORDER BY " . $win['order_by'];

$stmt = $state->DAL->my_query($sql);

if($err = $state->DAL->my_error()) {

$this->errors[] = $err;

return false;

}

$last_hash = "";

$hash = "";

$last_ob_hash = "";

$ob_hash = "";

while($row = $state->DAL->my_fetch_assoc($stmt)) {

#$sql = "select * from " . $state->table_name . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by'];

$sql = "SELECT *," . $state->winfunc_sql . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by'];

$stmt2 = $state->DAL->my_query($sql);

if($err = $state->DAL->my_error()) {

$this->errors[] = $err;

return false;

}

$done=array();

$rows=array();

while($row2=$state->DAL->my_fetch_assoc($stmt2)) {

$rows[] = $row2;

}

$last_hash = "";

$last_ob_hash = "";

$i = 0;

$rowlist="";

$rank = 0;

while($i<count($rows)) {

$row2 = $rows[$i];

++$rank;

$ob_hash = $row2["wf{$num}_obhash"];

$rowlist=$row2['wf_rownum'];

for($n=$i+1;$n<count($rows);++$n) {

$row3 = $rows[$n];

$new_ob_hash = $row3["wf{$num}_obhash"];

if($new_ob_hash != $ob_hash) {

break;

}

$rowlist .= "," . $row3['wf_rownum'];

++$i;

++$rank;

}

$dist = $rank/count($rows);

$sql = "UPDATE " . $state->table_name . " SET wf{$num} = {$dist} WHERE wf_rownum in ({$rowlist})";

$state->DAL->my_query($sql);

if($err = $state->DAL->my_error()) {

$this->errors[] = $err;

return false;

}

++$i;

}

return true;

}