MYSQL(5.0)のソートについて質問です。


次のようなデータがあります。

日付, 店舗, 面積
1, A0/C0/E1, 50
2, B0, 40
3, E1, 20
4, B1, 20
5, C1/C2, 10
6, B2, 20
7, D3, 30

A0/C0/E1はA0,C0,E1の合計を表します。
また、0は当該店舗全てを、1、2はその一部分を表します(C1やC2は、C0の一部)。

これを次のように並べ替えたいと思います。
日付, 店舗, 面積
1, A0/C0/E1, 50
5, C1/C2, 10
3, E1, 20
2, B0, 40
4, B1, 20
6, B2, 20
7, D3, 30

並べ替えの順序としては、
1.もっとも大きい店舗の集合(例では A0/C0/E1)をはじめに置く。
2.つぎに1に含まれる下位の集合(例では C1/C2, E1)があればそれらを並べる。
3.上記に含まれない店舗の集合を大きい順に並べる(例ではB0, B1, B2)。

上記のように並べ替える方法がありましたら教えてください。
よろしくお願いいたします。

補足:
A0/C0/E1 と A0/B1/C1というように一部分が重なるようなデータはありません(完全に含むか、全く含まないデータしかありません)。

回答の条件
  • 1人5回まで
  • 登録:2008/07/24 16:54:06
  • 終了:2008/07/25 16:54:25

回答(2件)

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012008/07/24 20:40:32

ポイント48pt

動作確認していないのですがいかがでしょう?

SELECT T3.*
FROM (SELECT *
        ,FIND_IN_SET("/",REPLACE(店舗,"/",",")) AS 店舗数
        ,IF((SELECT COUNT(*) FROM テーブル名 AS T2 WHERE INSTR(T2.店舗,"/")=0 AND INSTR(T1.店舗,T2.店舗)>0)>0,1,0) AS 下位
    FROM テーブル名 AS T1
) AS T3
ORDER BY T3.店舗数 DESC,T3.下位 DESC,T3.面積 DESC;

店舗数や下位が何を表しているかを示すため全てのフィールドを返していますが

結果セットとして日付,店舗,面積だけでよろしければ一行目を

SELECT T3.*

から

SELECT T3.日付,T3.店舗,T3.面積

に変えてください。

id:gillsderais

ありがとうございます。

 

ただ、うまく動きません。

結果として得られるのはこんな感じです。

 

 

日付, 店舗, 面積, 店舗数,下位

1, A0/C0/E1, 50, 0, 1

2, B0, 40, 0, 1

7, D3, 30, 0, 1

3, E1, 20, 0, 1

4, B1, 20, 0, 1

6, B2, 20, 0, 1

5, C1/C2, 10, 0, 0

 

 

なんかすごくおしいような気がしますが、

当方初心者なため、ご提示いただいたSQLを十分理解できず、

誤りの箇所が見つけられません。

 

ひきつづき、ご回答のほどよろしくお願いいたします。

(可能であれば、どのような操作を行ったのか説明していただけると助かります)

2008/07/25 09:51:43
id:kn1967 No.2

kn1967回答回数2915ベストアンサー獲得回数3012008/07/25 14:42:20

ポイント48pt

あいもかわらずテスト環境が使えず、脳内だけで申し訳ありませんが・・・。


店舗数が全てゼロになる点は下記のように書き直してくださいませ。

,FIND_IN_SET('/',REPLACE(T1.店舗,'/',',/,')) AS 店舗数

この式の意味はFIND_IN_SETがカンマで区切られた複数の文字列の中から特定の文字列の個数を探します。

その前段階としてREPLACEでカンマをいれてあります。

A0/C0/E1 → A0,/,C0,/,E1 → /は2
C1/C2 → C1,/,C2 → /は1
E1 → E1 → /はゼロ

これらは区切りの数を数えているだけなので、実際の店舗数を求めるためには全て+1が必要なのだけれど

1.もっとも大きい店舗の集合(例では A0/C0/E1)をはじめに置く。

という並び順として使えれば良いので省きました。


下位の計算がおかしい点は下記のように書き直してくださいませ。

,IF((SELECT COUNT(*) FROM テーブル名 AS T2 WHERE INSTR(T1.店舗,'/')=0 AND INSTR(T2.店舗,T1.店舗)>0)>0,1,0) AS 下位

店舗に / が含まれず、かつ、他のレコード内に含まれれば下位には1が入る。

複数店舗の合計の場合と、どこにも属さない場合にはゼロが入る

こちらは

2.つぎに1に含まれる下位の集合(例では C1/C2, E1)があればそれらを並べる。

を満たすためだけに使う


SELECT T3.*
FROM (SELECT *
        ,FIND_IN_SET('/',REPLACE(T1.店舗,'/',',/,')) AS 店舗数
        ,IF((SELECT COUNT(*) FROM テーブル名 AS T2 WHERE INSTR(T1.店舗,'/')=0 AND INSTR(T2.店舗,T1.店舗)>0)>0,1,0) AS 下位
    FROM テーブル名 AS T1
) AS T3
ORDER BY T3.店舗数 DESC,T3.下位 DESC,T3.面積 DESC;
id:gillsderais

1.Find_in_setですが、引数と一致する値の文字列の位置を返すので、「/」が含まれる場合は、全て「2」を返してしまいます。

 また、「ORDER BY T3.店舗数」となっているので、複数店舗の集合が上位に来てしまいます(たとえば「B1/D2/F0」があった場合、「A0/C0/E1」に含まれないのにその直下に来てしまいます)。

 

2.「他のレコード内に含まれれば下位には1」とありますが、すべてそれ自身に含まれてしまうので1になってしまいませんか?

 

結果的に、

日付, 店舗, 面積, 店舗数,下位

1, A0/C0/E1, 50, 2, 0

5, C1/C2, 10, 2, 0

2, B0, 40, 0, 1

7, D3, 30, 0, 1

3, E1, 20, 0, 1

4, B1, 20, 0, 1

6, B2, 20, 0, 1

 

こんな感じになりました。

2008/07/25 15:47:07
  • id:pahoo
    要件の確認をお願いします。
    1)店舗名は、必ず「アルファベット1文字+数字1文字」という構造ですか?
    2)店舗名の数字部分が「0」の場合に限り、「1,2,3‥‥」の部分集合を持つということですか? また、「1,2,3‥‥」は排他関係にありますか? つまり、「B1」「B2」は「B0」に含まれるが、「B1」と「B2」は含む/含まないの関係にはないということですか?

    (アルファベットと数字を別々のレコードに分解してから処理した方が簡単・確実だと思うのですが‥‥)
  • id:gillsderais
    ありがとうございます。

    1)文字数は全て同じです。
    2)1,2,3の下位集合(たとえば、B11、B12など)は今のところないです。B1、B2...は全て排他的です。同じくA0,B0,C0....も排他的です。

    アルファベットと数字を別々のレコードとはどういうことでしょうか?
    ちなみに「A0/C0/E1」の面積は分けられません(A0,C0の面積がわからないということです)。

    引き続きよろしくお願いいたします。
  • id:chuken_kenkou
    (1) E1がB0より上なのは、何で判断すればいいのでしょうか?
    (2) A0/C0/E1、C1/C2のような上位の集合というのは、どんなパターンがあるのですか?
  • id:gillsderais
    コメントありがとうございます。

    (1)E1が上なのは、E1が「A0/C0/E1」に含まれ、B0が含まれていないためです。
    (2)全ての組み合わせが考えられます。ただし、各上位の集合は、それぞれを完全に内包するか、全く含まないかのいずれかです。「A0/B0/C0」があった場合、「D0/E1」はありえますが、「B1/D0/E0」はありえません。

    ちなみに次のような場合、

    日付, 店舗, 面積
    1, A0/C0/E1, 50
    2, B0/D0, 40
    3, E1, 20
    4, B1, 20
    5, C1/C2, 10
    6, B2, 10
    7, D3, 5

    このようになります
    日付, 店舗, 面積
    1, A0/C0/E1, 50     (もっとも大きい集合)
    5, C1/C2, 10     (1に含まれる)
    3, E1, 20     (1に含まれる)
    2, B0/D0, 40     (1とは別の集合)
    4, B1, 20     (2に含まれる)
    6, B2, 10     (2に含まれる)
    7, D3, 5     (2に含まれる)
  • id:kn1967
    >それ自身も含まれる
    INSTR(T1.店舗,'/')=0 AND INSTR(T2.店舗,T1.店舗)>0
       ↓
    INSTR(T1.店舗,'/')=0 AND T2.店舗<>T1.店舗 AND INSTR(T2.店舗,T1.店舗)>0


    関数の使い方(FIND_IN_SET)を間違えてますし、単純に店舗数でのソートはなく、
    それに含まれる場合も考慮しなければならない旨のコメントが
    コメント欄にて追記されている事に気付いておりませんでした。
    それらの点についてはテスト環境が整い次第、確認の上で追記させていただきたく思いますが
    それまでに、他の方から回答があった場合は追加作業は行いません事、
    あしからずご了承ください。
  • id:gillsderais
    こちらこそ、説明不足ですみません^^;
    また、丁寧な説明を頂き、すこしずつSQLのことが理解できました。ありがとうございます。

    ところで、アルファベットだけのデータなら・・・

    SELECT T0.日付, length(T0.店舗) AS ls0, length(T1.店舗) as ls1
    , T0.店舗, T1.店舗, T0.面積
    FROM テーブル名 AS T0, テーブル名 AS T1
    WHERE T1.店舗 like Concat('%',replace(T0.店舗,'/','%'),'%')
    group by T0.店舗
    ORDER BY ls1 desc, ls0 desc, T0.店舗;

    でそれっぽいのものが出てきました・・・
    Pahooさんのおっしゃっていた数字とアルファベットを分けるとはこういうことなんですかね

    データ自体を変えることも念頭におかなければならないのかなぁ、と悩んでおります。
  • id:kn1967
    アルファベットと数値に分けるというのは
    アルファベットと数値それぞれの命名方法に規則性を見出せないかという事でしょう。

    私の回答は、命名法に頼らない方法です。
    (はずかしながら、まだ動いていませんが・・・)
  • id:gillsderais
    私もいまいち理解できていないませんが・・・

    ただ、分けようとすると
    「B1」などは「B」と「1」に分けられますが、
    「A0/C0/E1」の場合はどうすればよいのかわかりません。

    あと、もっとも単純にする方法は、全て最小単位に分割してアルファベットのみ(または数字のみ)で入力することかもしれません。
     
    たとえば、
     
    A0 → A1,A2, ・・・ → A,B,・・・
    B0 → B1,B2, ・・・ → J,K,・・・
    C0 → C1,C2, ・・・ → S,T,・・・
     
    に変換して、
     
    「A0/B2/C1」であれば、値を「A/B/・・・/K/S」とするような・・・
  • id:gillsderais
    kn1967 様

    申し訳ありませんが質問を終了することにします。
     
    根本的なところ(データの変換を含め)から考え直すことにします。
    いろいろ勉強になりました。誠にありがとうございました。
  • id:kn1967
    アルファベット1文字と数字1桁だと固定した場合の別案

    日付, 店舗, 面積, 母集団, 母桁数
     1, A0/C0/E1, 50, A0/C0/E1, 8 
     2, B0/D0, 40, B0/D0, 5
     3, E1, 20, A0/C0/E1, 8
     4, B1, 20, B1, 2
     5, C1/C2, 10, C1/C2, 5
     6, B2, 10, B2, 2
     7, D3, 5, B0/D0, 5
    上記のような項目を追加するクエリを作成して
    それをサブクエリとして母桁数 DESC、母集団、面積 DESCでソート。
    (面積は母集団よりも小さくなるという前提です)

    とりあえずですが、いかがでしょう?
  • id:gillsderais
    あっ、すみません^^;

    その点からも検討させていただきます。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません