Hi,

I was a little confusing.  The query orders of the order by columns (salary is project out into the temporary table) but, later ob_hash is used.  This is because the frame extends to all the values the order the same, and comparing multiple columns is hard, so a hash is used instead.

--Justin

On Tue, Mar 3, 2015 at 1:28 PM, Justin Swanhart <greenlion@gmail.com> wrote:

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);  +-----------+-------+--------+------------------+

| depname   | empno | salary | ss               |

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

| 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;

  }

On Tue, Mar 3, 2015 at 12:55 PM, Igor Babaev <igor@askmonty.org> wrote:

On 03/03/2015 10:25 AM, Sergei Golubchik wrote:
> Hi, Igor!
>
> On Mar 03, Igor Babaev wrote:
>>>
>>>> I'd also like to discuss window functions too.  I've implemented them
>>>> in shard-query and have ideas about how to implement them in the
>>>> server, but pluggable parser would be really useful here.
>>>
>>> Window functions have a good chance of being in 10.2, it's MDEV-6115.
>>> But I don't think that somebody is working on MDEV-6115 yet.
>>
>> I started working on MDEV-6115 some time ago.
>
> Ah, great. Sorry, I didn't know it.
> Could you then discuss it with Justin, please?
> See above, he has some ideas about the implementation.


Justin,
How do you prefer discussing your ideas?
On IRC (#maria) or by phone? (I don't have skype at the moment)

Regards,
Igor.

>
> Regards,
> Sergei


_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp