ある会社の 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 のユーザーとグループ管理によく使われるコマンドの概要
目次1. 関数デバウンス1. 画像安定化とは何ですか? 2. 関数のスロットリング2.1 タイマーの...
以前、私は自分で WordPress を構築していましたが、当時はサードパーティの仮想ホストを使用し...
1. ローカルyumソースを設定する1. ISOイメージをマウントする マウント -o loop /...
1. セットアップを始める次のコード関数を簡単に紹介します。 ref 関数を使用して変数の変更を監視...
JavaScript は現在、毎年新しいバージョンがリリースされており、より便利で効率的な新しい演算...
序文Linux では、コンパイルとリンクには Makefile を使用する必要がありますが、適切な ...
コードをコピーコードは次のとおりです。 <!--doctype はドキュメント タイプ htm...
コンテンツ タイプについて学ぶには、まずそれが何であるか、そして何に使用されるかを知る必要があります...
この記事では、タブバーの切り替えコンテンツバーを簡単に実現するためのJavaScriptの具体的なコ...
この記事では、MySQL 5.7.20のインストールと設定方法を参考までに紹介します。具体的な内容は...
MySQL 起動エラーWindows 10 に MySQL をインストールする前は、net star...
目次スケルトンスクリーンの使用Vueアーキテクチャスケルトンスクリーンアイデアの概要抽象コンポーネン...
Linux で大量のファイルを削除する効率をテストします。まず500,000個のファイルを作成する$...
3日間さまざまな困難に遭遇した後、ようやくこの機能を実現しました。正常に実装できる方法を見つける前に...
rpmインストールパッケージを使用してmysqlをオフラインでインストールします。参考までに準備:公...