Q&A
LIKE が group by の後ろにあるのは変ではないでしょうか。
データの選択条件は where で指定するべきもので、それからどの列の値で纏めるのかを指定するのが group by だと思いますが。
Javaを使ってAndroidの家計簿アプリを作成しています。
DBはAndroid Roomを使用しており、ここではSQLに関しての質問になります。
下記はDBにアクセスするDAOクラスに記載したSQLで、
ここでやりたいことは、元となるデータが記載されたテーブル「budget_tracker_table」からselectで抽出/集計した結果を、もう一つのテーブル「budget_tracker_table_alias」にinsertする事です。
下記では、SQLの内容を少し変え、Group by product_typeとしました。
意図としては、ユーザ入力値の日付(from)、日付(to)、店名から、指定された日付の間で、指定の店名で購入した商品のリストを取得し、さらにそのリストの中で、商品種類（product_type）を洗い出し、どういった商品の種類（電化製品、食品など）が購入されたかをパーセンテージで表示するSQLを組みたいと思っています。
BudgetTrackerAliasDao.java
@Query("insert into budget_tracker_table_alias (date_alias, store_name_alias, product_name_alias, product_type_alias, price_alias, product_type_percentage) select date, store_name, product_name, product_type, price, count(product_type) * 100.0 / (select count(*) from budget_tracker_table where date >= :date1 and date <= :date2 and store_name LIKE '%' || :storeName || '%') as 'Percentage' from budget_tracker_table where date >= :date1 and date <= :date2 and store_name LIKE '%' || :storeName || '%' group by product_type;") void insert(String date1, String date2, String storeName);
想定では、上記SQLを実行すると、ユーザ入力値のdate1（始まりの日付）、date2（終わりの日付）、storeName（店名）を元に、
budget_tracker_tableからデータを抽出/集計し、その結果をbudget_tracker_table_aliasにinsertするはずでした。
また、日付の間隔が大きければ、複数のデータが入るはずだったのですが、実際に実行してみると、
budget_tracker_table_aliasにinsertされたレコード数は想定よりも少ない結果となりました。
抽出元となるbudget_tracker_tableのデータは下記のようになっています。
2 2020-01-02 Google Store Pixel 6 Pro gadget 125000 17 2022-4-22 Amazon echo show 333 gadget 30000 18 2022-4-22 amazon tiger mask toy 1500 19 2022-4-22 Google store pixelbook gadget 45099 20 2022-4-22 Google store echo show gadget 1500 23 2022-4-26 Amazon echo show 2222 gadget 159999 24 2022-4-23 amazon renpho smart nawatobi gadget 3000 25 2022-4-26 Google store pixel 6a gadget 54000
上記状態で、下記SQLを実行します。
insert into budget_tracker_table_alias (date_alias, store_name_alias, product_name_alias, product_type_alias, price_alias, product_type_percentage) select date, store_name, product_name, product_type, price, count(product_type) * 100.0 / (select count(*) from budget_tracker_table where date >= :date1 and date <= :date2 and store_name LIKE '%' || :storeName || '%') as 'Percentage' from budget_tracker_table where date >= :date1 and date <= :date2 and store_name LIKE '%' || :storeName || '%' group by product_type;
budget_tracker_table_aliasには下記のデータが入りました。
ただ、SQLではLIKE検索でamazonとしているので、本来であればID「17」「23」が入っているはずだったのですが、
当該レコードのみが入っていませんでした。
146 2022-4-23 Amazon renpho smart nawatobi gadget 3000 75.0 147 2022-4-22 amazon tiger mask toy 1500 25.0
当該事象解決の為、ご助力頂けますと幸いです。
ご回答ありがとうございます。
ご指摘通り、group by句からLIKEを消し、where句に追記したところ、
少なくとも指定外の店名が入ることはなくなりました。
ただ、上記でも編集・追記しましたが、本来入るべきレコードが一件入っておらず、
こちらに関して解決の為の提案などがあれば、ご教示お願いいたします。
作成されている SQL は、 insert 文だったり select 文だったり集計していたりと、幾つかの文が組み合わさっています。
insert 文や集計の select は対象データが定まらなければ意味がありませんので、まずは対象データを抽出する select 文だけをテストしたほうが良いように思います。
group by store_name でお店ごとに集計してるので、SQL 的には集約関数を使ってないカラム (date, product_name, product_type, price) を指定するとエラーになるはずですが、SQLite だと通っちゃう (グループの中から適当な行の情報が出てる) ようですね。
お店ごとの集計なので、小文字の amazon のように 1 件しかないのが正常で、大文字の Amazon が 2 件出てくるのはおかしいのですが、よく見たら Amazon の後ろにスペースが…。
ご回答ありがとうございます。
確かにご指摘の通り、店名にスペースが入っていたりしており、
スペースを消した状態で再度試してみましたが、やはり意図した結果は得られませんでした。
ご指摘の中で、集計関数を使っていないカラムを指定するとエラーになるはずとのことですが、
意図した通り、指定日付内のデータ全てを抽出するためのヒントがあれば、ご教示頂けますと幸いです。
どういう結果を期待してますか？
日付で2022-4-22から2022-4-26を指定し、店名でAmazonを指定した場合、
現状のDBでは下記4つのレコードを抽出・集計した結果がbudget_tracker_table_aliasに入ると期待していました。
17 2022-4-22 Amazon echo show 333 gadget 40000
18 2022-4-22 amazon tiger mask toy 1500
23 2022-4-26 Amazon echo show 2222 gadget 159999
24 2022-4-23 Amazon renpho smart nawatobi gadget 3000
ただ、よくよく考えるとgroup by product_nameとしているので、
budget_tracker_table_aliasに入った集計結果では、gadgetが75%で、toyが25%だったので、
集計結果の数値的には間違ってないんですよね。。。
なので、結果としては、期待していた集計結果が取れているので、
問題ないのかなという結論に達しました。
ヒントを色々頂きありがとうございました。
回答1件
0
自己解決
よくよく考えたら、集計結果が間違っておらず、
期待していた数値を導き出せていたので、解決といたします。
投稿2022/05/13 23:37
総合スコア123
あなたの回答
tips
プレビュー
