MySQL group by date, force to return null value if does not exists -

-

i built query, let me return average ranking of apps.

but of them, month, don't have data, cause app new 1 (let's say, app out since month, , collecting data since month)

select      date_format(date, '%y-%m'),     app_id,     avg(rank) wadstats.applestore_ranking app_id in (100,         2,         3,         4,         5,         6) group month(date), app_id order case when app_id = 100 1 else 2 end, date asc

i in need show first app_id = 100

but app_id = 8, don't have data august exemple.

then results looks like

'2015-07', '100', '3.9355' '2015-04', '100', '49.5000' '2015-08', '100', '5.2258' '2015-05', '100', '16.3333' '2015-09', '100', '6.1333' '2015-06', '100', '7.5667' '2015-10', '100', '5.7727' '2015-04', '2', '6.0000' '2015-08', '2', '9.8710' '2015-05', '2', '6.4667' '2015-09', '2', '8.9667' '2015-06', '2', '8.5333' '2015-10', '2', '9.9545' '2015-07', '2', '10.5806' '2015-05', '3', '56.3929' '2015-09', '3', '55.1667' '2015-06', '3', '35.2500' '2015-07', '3', '38.7143' '2015-04', '3', '38.7500' '2015-08', '3', '52.5500' '2015-09', '4', '30.2105' '2015-06', '4', '27.9231' '2015-10', '4', '30.0000' '2015-07', '4', '47.0000' '2015-08', '4', '32.6818' '2015-06', '5', '46.8667' '2015-10', '5', '86.6667' '2015-07', '5', '63.5185' '2015-04', '5', '24.2500' '2015-08', '5', '67.3571' '2015-10', '6', '30.1818'

i wish instead have null each month if no data available specific month

expected results 

'2015-07', '100', '3.9355' '2015-04', '100', '49.5000' '2015-08', '100', '5.2258' '2015-05', '100', '16.3333' '2015-09', '100', '6.1333' '2015-06', '100', '7.5667' '2015-10', '100', '5.7727' '2015-04', '2', '6.0000' '2015-08', '2', '9.8710' '2015-05', '2', '6.4667' '2015-09', '2', '8.9667' '2015-06', '2', '8.5333' '2015-10', '2', '9.9545' '2015-07', '2', '10.5806' '2015-05', '3', '56.3929' '2015-09', '3', '55.1667' '2015-06', '3', '35.2500' '2015-07', '3', '38.7143' '2015-04', '3', '38.7500' '2015-08', '3', '52.5500' '2015-09', '4', '30.2105' '2015-06', '4', '27.9231' '2015-05', '4', null '2015-10', '4', '30.0000' '2015-07', '4', '47.0000' '2015-08', '4', '32.6818' '2015-06', '5', '46.8667' '2015-10', '5', '86.6667' '2015-07', '5', '63.5185' '2015-04', '5', '24.2500' '2015-08', '5', '67.3571' '2015-04', '6', null '2015-05', '6', null '2015-06', '6', null '2015-07', '6', null '2015-08', '6', null '2015-09', '6', null '2015-10', '6', '30.1818'

if need have 0 instead of null, ok too, need have each month in db, have value each app_id

thanks advance

the query below uses derived table (alias inr) put yearmonth/app_id combinations. uses in left join fetch data whether or not exists in table applestore_ranking.

use ifnull() if want 0 appear opposed null. part become ifnull(avg(r.rank),0) rank, instead.

note, 1 put staging highlight code inr select alone , see simple output. make left join, follows, easier understand.

the concept of helper table used time in sql. put on fly, , dropped. other times permanent.

schema

create schema applesandbox; use applesandbox;  -- drop table applestore_ranking; create table applestore_ranking (   id int auto_increment primary key,     app_id int not null,     date date not null, -- not great column name     rank int not null ); -- truncate table applestore_ranking; insert applestore_ranking (app_id,date,rank) values (2,'2015-08-01',1),(2,'2015-09-05',10),(2,'2015-09-12',11),(2,'2015-10-01',14), (6,'2015-10-01',7),(6,'2015-10-05',6),(6,'2015-10-14',2), (100,'2015-09-01',16),(100,'2015-10-01',16),(100,'2015-10-05',17),(100,'2015-10-14',18);  create table monthhelper (   -- load few years worth     id int auto_increment primary key,     thedate date not null, -- better column name        wanttosee int not null -- want see in results or not? 0=no, 1=yes );  -- note few wanttosee have been turned on 1 insert monthhelper(thedate,wanttosee) values ('2015-05-01',0),('2015-06-01',0),('2015-07-01',0),('2015-08-01',1),('2015-09-01',1),('2015-10-01',1),('2015-11-01',0),('2015-12-01',0), ('2016-01-01',0),('2016-02-01',0),('2016-03-01',0); -- etc

the query

select date_format(inr.thedate, '%y-%m') yearmonth, inr.app_id, avg(r.rank) rank (   select distinct mh.thedate,r.app_id     monthhelper mh     cross join applestore_ranking r     mh.wanttosee=1     , r.app_id in (100,2,3,4,5,6) ) inr left join applestore_ranking r on r.app_id=inr.app_id , year(inr.thedate)=year(r.date) , month(inr.thedate)=month(r.date) group month(inr.thedate), inr.app_id order case when inr.app_id = 100 1 else 2 end, inr.thedate asc

the results

+-----------+--------+---------+ | yearmonth | app_id | rank    | +-----------+--------+---------+ | 2015-08   |    100 |    null | | 2015-09   |    100 | 16.0000 | | 2015-10   |    100 | 17.0000 | | 2015-08   |      2 |  1.0000 | | 2015-08   |      6 |    null | | 2015-09   |      2 | 10.5000 | | 2015-09   |      6 |    null | | 2015-10   |      2 | 14.0000 | | 2015-10   |      6 |  5.0000 | +-----------+--------+---------+

cleanup

drop schema applesandbox;

Comments

Post a Comment

Popular posts from this blog

php - Invalid Cofiguration - yii\base\InvalidConfigException - Yii2 -

-
Image
i've installed yii2-user module/dektrium yii2-app-basic application command composer require "dektrium/yii2-user:0.9.*@dev" config/console.php return [ . . 'modules' => [ 'gii' => 'yii\gii\module', 'user' => [ 'class' => 'dektrium\user\module', ], ], . . ] config/web.php 'components' => [ . . /* 'user' => [ 'identityclass' => 'app\models\user', 'enableautologin' => true, ], */ 'modules' => [ 'user' => [ 'class' => 'dektrium\user\module', ], ], . . ] after that, run command $ php yii migrate/up --migrationpath=@vendor/dektrium/yii2-user/migrations updating database schema . but, when run http://localhost/mylawsuit/yii/web/index.php?r=u
Read more

How to show in django cms breadcrumbs full path? -

-
need advice - how show in breadcrumbs whole path article or page? i've got code: {% ance in ancestors %} <li> {% if not forloop.last %} <a href="{{ ance.get_absolute_url }}">{{ ance.get_menu_title }}</a> {% else %} <span class="active">{{ ance.get_menu_title }}</span> {% endif %} </li> {% endfor %} but shows "home" , current page. how modify show whole path in breadcrumbs? i've found in file menu_tags.py code calls ancestors. @ least there class showbreadcrumbs: class showbreadcrumb(inclusiontag): """ shows breadcrumb node has same url current request - start level: after level should breadcrumb start? 0=home - template: template used render breadcrumb """ name = 'show_breadcrumb' template = 'menu/dummy.html' options = options( argument('start_level', default=0, require
Read more

ruby on rails - npm error: tunneling socket could not be established, cause=connect ETIMEDOUT -

-
i have app uses rails framework , implements angularjs part of front end. i have pushed heroku , have heroku toolbelt installed, when try migrate db using "heroku run rake db:migrate" receive following error(s): installing core plugins heroku-cli-addons, heroku-apps, heroku-fork, heroku-git, heroku-local, heroku-run, heroku-status... error installing package. try running again gode_debug=info see more output. ! `run` not heroku command. ! perhaps meant `-h`, `2fa`, `auth`, `join`, `open`, `orgs`, `pg`, `ps` or `rake`. ! see `heroku help` list of available commands. i run command "gode_debug=info heroku run rake db:migrate" , receive error: npm err! darwin 14.5.0 npm err! argv "/users/christopher_pelnar/.heroku/node-v4.2.1-darwin-x64/bin/node" "/users/christopher_pelnar/.heroku/node-v4.2.1-darwin-x64/lib/node_modules/npm/cli.js" "install" "heroku-cli-addons" "heroku-apps" "heroku-fork
Read more