select文でグループに連番を振るには

Question

PostgreSQL 9.5を使用しています。
以下のようなテーブルがあります。
date, from, to
2016/10/10, tokyo, hokkaido
2016/10/10, osaka, hokkaido
2016/10/10, osaka, tokyo
2016/10/11, osaka, tokyo
2016/10/11, fukuoka, tokyo
2016/10/12, osaka, hokkaido

dateとtoを一括りにして連番を降り、select文で以下のような感じで抜きたいと思っています。
id,date, from, to
1,2016/10/10, tokyo, hokkaido
1,2016/10/10, osaka, hokkaido
2,2016/10/10, osaka, tokyo
3,2016/10/11, osaka, tokyo
3,2016/10/11, fukuoka, tokyo
4,2016/10/12, osaka, hokkaido

どのようなselect文を書けばよいか教えていただけないでしょうか。
分析関数を使って色々と試してみたのですがうまくいきませんでした。
どうぞよろしくお願い致します。

Comments

[退会済みユーザー]

PostgreSQL のバージョン等　実行環境（開発環境）の情報を記載してください。

[an6]

ご指摘ありがとうございます、PostgreSQL 9.5を使用しております。

Answer 1

windows 関数を利用して

SELECT dense_rank() OVER (ORDER BY dateA,toA ASC), *
FROM tableA
ORDER BY dateA, toA ASC, fromA DESC;

テーブル定義
create table tableA (dateA date, fromA text, toA text);

ちょと強引ですが、作ってみました。

create table tableAA (number int,dateA date, fromA text, toA text);

insert into tableAA SELECT dense_rank() OVER (ORDER BY dateA,toA ASC) + max, t1.* FROM tableA t1, (select max(number) max from tableAA) t2 ORDER BY  fromA DESC;

Comments

[A.Ichi]

既にdense_rank()を使った回答が有りました。失礼いたしました。

[an6]

とんでもないです、ありがとうございます。

select文でdense_rankを使用した結果をそのまま別のテーブルにinsertする運用を考えております。insert先のテーブルには累積していくイメージです。
一方でselect文で引いてくる元のテーブルは一回一回パージするため、同じようにdense_rankを使用するとinsertしたものと同じ連番が１から採番されてしまいます。
1,2,3でパージした後4,5,6とつながるようにしたいのですが、この場合はdense_rankではない別の方法を利用すべきでしょうか？？

[A.Ichi]

一応できますレベルのSQLを作成してみました。

[an6]

日々の仕事が忙しくしばらく趣味のプログラミングから離れてしまい返事がおそくなってしまい申しわけありません。
教えていただいた方法で試しました、できました！！
既存のテーブルの最大値を取ってきて、そこにdense rankを重ねると連番になるのですね。勉強になりました、本当にありがとうございました。

Answer 2

「分析関数を使って色々と試してみた」の内容が不明なのですが・・・、
例えば以下のようなSQLではいかがですか？

SELECT   DENSE_RANK() OVER(ORDER BY date,
                                    "to")  AS id,
         TO_CHAR(date, 'YYYY/MM/DD')       AS date,
         "from"                            AS from,
         "to"                              AS to
FROM     test_table
ORDER BY id,
         "from" DESC

id	date	from	to
1	2016/10/10	tokyo	hokkaido
1	2016/10/10	osaka	hokkaido
2	2016/10/10	osaka	tokyo
3	2016/10/11	osaka	tokyo
3	2016/10/11	fukuoka	tokyo
4	2016/10/12	osaka	hokkaido

Comments

[an6]

ありがとうございます、dense_rank()を使用すればよかったのですね。
教えていただいた通りにやったら実現することができました。