ある会社の BI 職の面接を受けたとき、面接で SQL に関する質問がありました。一見すると非常に簡単に思えましたが、書いてみると要約が足りず、すぐに書き出すことができませんでした。 トピックは次のとおりです。 各ブランドのプロモーション日数を調べる テーブル sale はプロモーション マーケティング テーブルです。データには重複した日付があります。たとえば、id 1 の end_date は 20180905 で、id 2 の start_date は 20180903 です。つまり、id 1 と id 2 には重複した販売日があります。各ブランドのプロモーション日数を調べます (重複はカウントされません) 表の結果は次のとおりです。 +------+-------+------------+------------+ | ID | ブランド | 開始日 | 終了日 | +------+-------+------------+------------+ | 1 | ナイキ | 2018-09-01 | 2018-09-05 | | 2 | ナイキ | 2018-09-03 | 2018-09-06 | | 3 | ナイキ | 2018-09-09 | 2018-09-15 | | 4 | oppo | 2018-08-04 | 2018-08-05 | | 5 | oppo | 2018-08-04 | 2018-08-15 | | 6 | 生体内 | 2018-08-15 | 2018-08-21 | | 7 | 生体内 | 2018-09-02 | 2018-09-12 | +------+-------+------------+------------+ 最終結果は
テーブルステートメントの作成 -- ---------------------------- -- テーブル構造を販売中 -- ---------------------------- `sale` が存在する場合はテーブルを削除します。 テーブル「sale」を作成します( `id` int(11) デフォルト NULL, `brand` varchar(255) デフォルト NULL, `start_date` 日付 デフォルト NULL、 `end_date` 日付 デフォルト NULL )ENGINE=InnoDB デフォルト文字セット=utf8; -- ---------------------------- -- 販売記録 -- ---------------------------- `sale` に VALUES (1, 'nike', '2018-09-01', '2018-09-05') を挿入します。 `sale` に VALUES (2, 'nike', '2018-09-03', '2018-09-06') を挿入します。 `sale` に VALUES (3, 'nike', '2018-09-09', '2018-09-15') を挿入します。 `sale` に VALUES (4, 'oppo', '2018-08-04', '2018-08-05') を挿入します。 `sale` に VALUES (5, 'oppo', '2018-08-04', '2018-08-15') を挿入します。 `sale` に VALUES (6, 'vivo', '2018-08-15', '2018-08-21') を挿入します。 `sale` に値 (7、'vivo'、'2018-09-02'、'2018-09-12') を挿入します。 方法1: 次のレコードへの自己関連付けの方法を使用する ブランドを選択、合計(終了日-開始日+1)、全日数から ( s.idを選択し、 s.ブランド、 s.開始日、 s.end_date 、 if(s.start_date>=ifnull(t.end_date,s.start_date) ,s.start_date,DATE_ADD(t.end_date,interval 1 day) ) を before_date として sale s の左から join (select id+1 as id ,brand,end_date from sale) t on s.id = t.id and s.brand = t.brand s.idで並べ替え )tmp ブランド別にグループ化 運用結果 +-------+---------+ | ブランド | all_day | +-------+---------+ | ナイキ | 13 | | 反対 | 12 | | 生体 | 18 | +-------+---------+ この方法はこの質問の表には有効ですが、不連続な ID を持つブランドのレコードには適用できない可能性があります。 方法2: SELECT a.brand,SUM( 場合 a.start_date=b.start_date かつ a.end_date=b.end_date の場合 存在しない( 選択* sale c から LEFT JOIN sale d ON c.brand=d.brand ここで、d.brand = a.brand かつ、c.start_date=a.start_date かつ c.id<>d.id AND (d.start_date が c.start_date と c.end_date の間であり、d.end_date>c.end_date または c.start_date が d.start_date と d.end_date の間であり、c.end_date>d.end_date である) ) その後 (a.終了日 - a.開始日 + 1) (a.id<>b.id かつ b.start_date が a.start_date かつ a.end_date かつ b.end_date>a.end_date の間) の場合、(b.end_date-a.start_date+1) そうでなければ0終了 ) 毎日 sale a から sale b を JOIN し、 a.brand=b.brand を GROUP BY a.brand にします。 運用結果 +-------+----------+ | ブランド | all_days | +-------+----------+ | ナイキ | 13 | | 反対 | 12 | | 生体 | 18 | +-------+----------+ 条件の中には d.start_date が c.start_date と c.end_date の間であり、かつ d.end_date>c.end_date である または c.start_date が d.start_date と d.end_date の間であり、c.end_date が d.end_date より大きい 置き換え可能 c.開始日 < d.終了日 かつ (c.終了日 > d.開始日) 結果も正しい 分析関数を使うことも可能です。私のパソコンにはまだOracleがインストールされていないので、MySQLで書きました。 以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。 以下もご興味があるかもしれません:
|
<<: Linux のユーザーとグループ管理によく使われるコマンドの概要
MySQL スロー クエリ (正式名称はスロー クエリ ログ) は、MySQL によって提供されるロ...
目次データ量匿名マウントと名前付きマウントデータボリュームの場所データ量匿名マウントと名前付きマウン...
Web デザインは、インターネットの出現後に誕生した新興の周辺産業です。 Web ページは店頭のよう...
目次ストレージエンジンのメモリ管理データ ページを LRU キューの先頭に置かないのはなぜですか?ダ...
私たちウェブマスターは皆、ウェブサイトを最適化する際に記事内のキーワードを太字にすることが最適化に非...
最近、Grover の Web サイトで楽しいホバー アニメーションを見つけ、自分自身のインスピレー...
このケースはCentOS 7システムに基づいていますDockerの使用経験がある人に適していますLi...
先ほど MySQL パスワードを設定したのに、外食したり荷物を受け取ったりするときにパスワードを忘れ...
目次スケルトンスクリーンの使用Vueアーキテクチャスケルトンスクリーンアイデアの概要抽象コンポーネン...
コンテナが起動した後まず管理者にログインして新しいユーザーを作成してください $ docker ex...
CLion のプロセス全体を最初から説明します。CLion は、JetBrains がリリースした新...
袋を用意するインストールApacheがすでにインストールされているかどうかを確認するrpm -qa ...
以前は、フロートはレイアウトによく使用されていましたが、フローティングレイアウトを使用すると親要素の...
リスト形式のテキストの展開と折りたたみの実装は参考までに。具体的な内容は以下のとおりです。必要: 1...
特定の期間内に特定の IP へのアクセス回数を制限する方法は、特に悪意のある DDOS 攻撃に直面し...