sql server - SQL: Efficiently finding minimum data points within separate subranges for each column -


i have sql query (targeting sql server, sadly) looks this:

select     datetimefromparts(year(ts), month(ts), day(ts), 0, 0, 0, 0) day,     avg(case when datepart(hour, ts) = 0 val else null end) hr0,     avg(case when datepart(hour, ts) = 1 val else null end) hr1,     -- ...etc other hours...      sample_data group      datetimefromparts(year(ts), month(ts), day(ts), 0, 0, 0, 0)

this works well, calculating (for each day) average value each hour.

my requirements changing, however: i'm asked first sample each hour. thus, hr0 should reflect single value smallest ts satisfying datepart(hour, ts) = 0 (and, of course, still being within same day), or null if none exist.

the obvious approach comes mind using subquery each hour, strikes me significant decrease in runtime efficiency (and implementation attempts not slow ugly). there better alternative i'm not considering?

to truncate time component datetime can cast date.

look top-n-per-group or greatest-n-per-group. sql server see retrieving n rows per group.

here 1 possible variant using row_number().

the where rn=1 filter results in @ 1 row per hour. each row contain first val hour.

the group dt 24 min(case when datepart(hour, ts) = ... pivots result set make 1 row per day 24 columns each hour. here instead of min can put other aggregating function (max, sum, avg). result not change, because after first filter there can @ 1 row per hour.

with cte (     select         ts         ,cast(ts date) dt         ,val         ,row_number()              over(partition cast(ts date), datepart(hour, ts) order ts) rn     sample_data ) select     dt     ,min(case when datepart(hour, ts) = 0 val else null end) hr0     ,min(case when datepart(hour, ts) = 1 val else null end) hr1     ,min(case when datepart(hour, ts) = 2 val else null end) hr2     -- ...etc other hours... cte rn=1 group dt order dt;

here sql fiddle sample data.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more