39471


例えば、スタックオーバーフローの自分の信用度を定時観測していたとします。(今回やりたいことは、たぶん stackexchange の api
たたけば取得できそうですが、具体例として聞きたいので、 mysql の中で完結するような方法を探しています。)

そうすると、たとえば

[code]

    observed_at,reputation
    2017-11-1,1000
    2017-11-2,1000
    2017-11-3,1010
    2017-11-4,1010
    2017-11-5,1010
    2017-11-6,1045
    2017-11-7,1045
    2017-11-8,1045
    
[/code]

のようなデータが select できます。

この中から、 reputation に変化のあった最初のレコードたちを select したいと思いました。

[code]

    observed_at,reputation
    2017-11-1,1000
    2017-11-3,1010
    2017-11-6,1045
    
[/code]

### 質問

  * mysql で、観測日時・その値のレコード列があったときに、データ値が変化した最初の観測だけを select するようにはどうしたらよいでしょうか？

### 追記

  * 日付に漏れは、あっても対応できた方がうれしいです。何かしらの理由で定時観測が失敗する可能性があるので。。


CC BY-SA 3.0
2017-11-09T05:57:28.867
39447
2017-11-10T02:50:19.670
2017-11-09T08:49:15.717
754
754
question
mysql sql
mysql で定期観測したデータから、変更があったレコードのみ選択したい


型等が不明ですが、仮にdateとintegerと仮定して、  
最初の日はminで取得できます。  
変化があった＝違う日のデータが入っていたとするならば

[code]

    create table Test(observed_at date, reputation integer);
    insert into Test(observed_at, reputation) values("2017-11-1",1000);
    insert into Test(observed_at, reputation) values("2017-11-2",1000);
    insert into Test(observed_at, reputation) values("2017-11-3",1010);
    insert into Test(observed_at, reputation) values("2017-11-4",1010);
    insert into Test(observed_at, reputation) values("2017-11-5",1010);
    insert into Test(observed_at, reputation) values("2017-11-6",1045);
    insert into Test(observed_at, reputation) values("2017-11-7",1045);
    insert into Test(observed_at, reputation) values("2017-11-8",1045);
    
[/code]

maxとminをhavingで比較すればいいと思います。

[code]

    select min(observed_at), reputation from Test group by reputation having  max(observed_at) != min(observed_at);
    
[/code]

もし仮に同じ日付でデータが投入される可能性があり、それも変化があったとみなすのであれば  
countして1より大きければというhaving比較すればいいと思います。

[code]

    select min(observed_at), reputation from Test group by reputation having  count(observed_at) > 1 ;
    
[/code]


CC BY-SA 3.0
2017-11-09T07:18:20.137
39452
2017-11-09T07:18:20.137
22665
39447
answer


日付が連続しているという前提であれば、

[code]

    SELECT * FROM table t1 JOIN t2 ON t1.observed_at - t2.observed_at,reputation WHERE t1.observed_at != t2.observed_at
    
[/code]

こんな感じで、日付-1した自分自身とJOINして検索すればで解決出来ます。

日付が連続していないと、各行ごとに「1つ前の行」を検索で見つけてあげる必要があります。ウィンドウ関数をサポートしていれば簡単なのですが、MySQLはサポートしてないので、相関サブクエリで代替することになります。具体的に動くSQL提示できないので、「MySQL
LAG」で検索すると解説が見つかります。

私ならややこしいSQLを保守するぐらいであれば

  * プログラムで全件取得して処理する
  * 日付が連続するよう補完する
  * PostgreSQLでやる

ことを検討します。


CC BY-SA 3.0
2017-11-09T23:50:56.567
39468
2017-11-09T23:50:56.567
5793
39447
answer


個人的には、@suzukisさんのおっしゃる通り、プログラムでやるか、Postgresqlのような分析関数のあるDBでやるほうが素直かと思いますが、強引にやるとするなら、ユーザー変数を使ってrowidを採番すればできると思います。（２ステートメントになりますが・・・）

[code]

    set @rowid_l = 0, @rowid_r = 0;
    
    select l.observed_at, l.reputation
    from    (
        select *, @rowid_l := @rowid_l + 1 as rowid from Test order by observed_at
    ) as l
    left join (
        select *, @rowid_r := @rowid_r + 1 as rowid from Test order by observed_at
    ) as r
    on l.rowid = r.rowid + 1
    where r.rowid is null
        or l.reputation != r.reputation
    order by l.rowid
    ;
    
[/code]


CC BY-SA 3.0
2017-11-10T02:50:19.670
39471
2017-11-10T02:50:19.670
9608
39447
answer