sql - a specific way to use max() -


i have datetime, total, material, , mix columns.

this equipment runs mix x amount of hours, switches mix b x amount of hours. switches mix a.

i have query just: 

select max(total), material, mix  database  group material, mix

however need catch fact mix ran twice , 1 value recorded , wouldn't know how material used in 1 skipped.

is there way @ max(total), new max every time mix or material changes? (not actual different mix / material when changes mix b mix a.

edit show data looks like 

timestamp                  mix           material          tons   2017-02-08 07:49:25.000    mixa          mat1              30.05 2017-02-08 07:50:25.000    mixa          mat1              30.27 2017-02-08 07:51:25.000    mixa          mat1              30.50 2017-02-08 07:52:25.000    mixa          mat1              30.76 2017-02-08 07:53:25.000    mixa          mat1              31.01 2017-02-08 07:58:25.000    mixb          mat1              0.1 2017-02-08 07:59:25.000    mixb          mat1              0.8 2017-02-08 08:00:25.000    mixb          mat1              1.3 2017-02-08 08:01:25.000    mixb          mat1              2.0 2017-02-08 08:02:25.000    mixb          mat1              2.5 2017-02-08 08:10:25.000    mixa          mat1              0.1 2017-02-08 08:01:25.000    mixa          mat1              0.5

so, before first record shown above machine running amount of time. switched mixes, ran, switched , ended production day.

i'm trying find efficient way find amount of material used, because tons column running total , can't sum it. max() simple, miss second run of mix a.

it looks gap-and-islands me.

sample data

note, added mat2 show need partition , fixed timestamp of last entry.

declare @t table(ts datetime2(0), mix varchar(50), material varchar(50), tons float);  insert @t (ts, mix, material, tons) values ('2017-02-08 07:49:25', 'mixa', 'mat1', 30.05), ('2017-02-08 07:50:25', 'mixa', 'mat1', 30.27), ('2017-02-08 07:51:25', 'mixa', 'mat1', 30.50), ('2017-02-08 07:52:25', 'mixa', 'mat1', 30.76), ('2017-02-08 07:53:25', 'mixa', 'mat1', 31.01), ('2017-02-08 07:58:25', 'mixb', 'mat1', 0.1  ), ('2017-02-08 07:59:25', 'mixb', 'mat1', 0.8  ), ('2017-02-08 08:00:25', 'mixb', 'mat1', 1.3  ), ('2017-02-08 08:01:25', 'mixb', 'mat1', 2.0  ), ('2017-02-08 08:02:25', 'mixb', 'mat1', 2.5  ), ('2017-02-08 08:10:25', 'mixa', 'mat1', 0.1  ), ('2017-02-08 08:11:25', 'mixa', 'mat1', 0.5  ), ('2017-02-08 07:49:25', 'mixa', 'mat2', 30.05), ('2017-02-08 07:50:25', 'mixa', 'mat2', 30.27), ('2017-02-08 07:51:25', 'mixa', 'mat2', 30.50), ('2017-02-08 07:52:25', 'mixa', 'mat2', 30.76), ('2017-02-08 07:53:25', 'mixa', 'mat2', 31.01), ('2017-02-08 07:58:25', 'mixb', 'mat2', 0.1  ), ('2017-02-08 07:59:25', 'mixb', 'mat2', 0.8  ), ('2017-02-08 08:00:25', 'mixb', 'mat2', 1.3  ), ('2017-02-08 08:01:25', 'mixb', 'mat2', 2.0  ), ('2017-02-08 08:02:25', 'mixb', 'mat2', 2.5  ), ('2017-02-08 08:10:25', 'mixa', 'mat2', 0.1  ), ('2017-02-08 08:11:25', 'mixa', 'mat2', 0.5  );

query

with cte_rn (     select         ts         ,mix         ,material         ,tons         ,row_number() on (partition material order ts) rn1         ,row_number() on (partition material, mix order ts) rn2     @t ) ,cte_groups (     select         ts         ,mix         ,material         ,tons         ,rn1         ,rn2         ,rn1 - rn2 groupnumber     cte_rn ) select     material     ,mix     ,max(tons) maxtons     ,max(ts) maxts cte_groups group     material     ,groupnumber     ,mix order     material     ,maxts ;

result

+----------+------+---------+---------------------+ | material | mix  | maxtons |        maxts        | +----------+------+---------+---------------------+ | mat1     | mixa | 31.01   | 2017-02-08 07:53:25 | | mat1     | mixb | 2.5     | 2017-02-08 08:02:25 | | mat1     | mixa | 0.5     | 2017-02-08 08:11:25 | | mat2     | mixa | 31.01   | 2017-02-08 07:53:25 | | mat2     | mixb | 2.5     | 2017-02-08 08:02:25 | | mat2     | mixa | 0.5     | 2017-02-08 08:11:25 | +----------+------+---------+---------------------+

you can sum further add 31.01 , 0.5 mat1 if need it. add 1 more group material, mix. question not clear result should be.

how works

run query step-by-step, cte-by-cte understand how works.

"gaps , islands" classic problem. when mix changes, "island" followed "gap".

cte_rn calculates 2 sets of row numbers. difference between them number of group (cte_groups).

with ... select * cte_groups order material, ts;

produces

+---------------------+------+----------+-------+-----+-----+-------------+ |         ts          | mix  | material | tons  | rn1 | rn2 | groupnumber | +---------------------+------+----------+-------+-----+-----+-------------+ | 2017-02-08 07:49:25 | mixa | mat1     | 30.05 |   1 |   1 |           0 | | 2017-02-08 07:50:25 | mixa | mat1     | 30.27 |   2 |   2 |           0 | | 2017-02-08 07:51:25 | mixa | mat1     | 30.5  |   3 |   3 |           0 | | 2017-02-08 07:52:25 | mixa | mat1     | 30.76 |   4 |   4 |           0 | | 2017-02-08 07:53:25 | mixa | mat1     | 31.01 |   5 |   5 |           0 | | 2017-02-08 07:58:25 | mixb | mat1     | 0.1   |   6 |   1 |           5 | | 2017-02-08 07:59:25 | mixb | mat1     | 0.8   |   7 |   2 |           5 | | 2017-02-08 08:00:25 | mixb | mat1     | 1.3   |   8 |   3 |           5 | | 2017-02-08 08:01:25 | mixb | mat1     | 2     |   9 |   4 |           5 | | 2017-02-08 08:02:25 | mixb | mat1     | 2.5   |  10 |   5 |           5 | | 2017-02-08 08:10:25 | mixa | mat1     | 0.1   |  11 |   6 |           5 | | 2017-02-08 08:11:25 | mixa | mat1     | 0.5   |  12 |   7 |           5 | | 2017-02-08 07:49:25 | mixa | mat2     | 30.05 |   1 |   1 |           0 | | 2017-02-08 07:50:25 | mixa | mat2     | 30.27 |   2 |   2 |           0 | | 2017-02-08 07:51:25 | mixa | mat2     | 30.5  |   3 |   3 |           0 | | 2017-02-08 07:52:25 | mixa | mat2     | 30.76 |   4 |   4 |           0 | | 2017-02-08 07:53:25 | mixa | mat2     | 31.01 |   5 |   5 |           0 | | 2017-02-08 07:58:25 | mixb | mat2     | 0.1   |   6 |   1 |           5 | | 2017-02-08 07:59:25 | mixb | mat2     | 0.8   |   7 |   2 |           5 | | 2017-02-08 08:00:25 | mixb | mat2     | 1.3   |   8 |   3 |           5 | | 2017-02-08 08:01:25 | mixb | mat2     | 2     |   9 |   4 |           5 | | 2017-02-08 08:02:25 | mixb | mat2     | 2.5   |  10 |   5 |           5 | | 2017-02-08 08:10:25 | mixa | mat2     | 0.1   |  11 |   6 |           5 | | 2017-02-08 08:11:25 | mixa | mat2     | 0.5   |  12 |   7 |           5 | +---------------------+------+----------+-------+-----+-----+-------------+

then group material, groupnumber, mix final result.


-

Comments

Post a Comment

Popular posts from this blog

python - How to insert QWidgets in the middle of a Layout? -

Image
i'm using qt framework build graphical user interface. use qgridlayout position qwidgets neatly. gui looks this: my application regularly adds new widgets gui @ runtime. these new widgets not added @ end of qlayout, somewhere in middle. the procedure bit cumbersome. applied on figure above, need take out widg_c , widg_d , ... qgridlayout. next, add widg_x and widg_y , , put other widgets again. how remove widgets qgridlayout: for in reversed(range(mygridlayout.count())): self.itemat(i).widget().setparent(none) ### as long you're dealing small amount of widgets, procedure not disaster. in application display lot of small widgets - perhaps 50 or more! application freezes second while procedure ongoing, annoying user. there way insert widgets somewhere in qlayout, without need take out other widgets? edit: apparently solution qvboxlayout simple. use function insertwidget(..) instead of addwidget(..) . docs can found link: http://doc.qt.io/qt-5/qboxlayou
Read more

python - serve multiple gunicorn django instances under nginx ubuntu -

i need serve 2 webs apps (django) on gunicorn under different domain names using nginx on ubuntu. started using tutorial: https://www.digitalocean.com/community/tutorials/how-to-set-up-django-with-postgres-nginx-and-gunicorn-on-ubuntu-16-04 it worked fine 1 using that. tried same thing second gives me 502 gateway second domain. tried following tutorial: http://michal.karzynski.pl/blog/2013/10/29/serving-multiple-django-applications-with-nginx-gunicorn-supervisor/ i'm @ part run gunicorn start script: sudo bin/gunicorn_start i response: starting webuildblack root traceback (most recent call last): file "/home/devin/webuildblack/bin/gunicorn", line 7, in <module> gunicorn.app.wsgiapp import run importerror: no module named 'gunicorn.app'; 'gunicorn' not package you have named program gunicorn.py gunicorn -package. rename file , remove gunicorn.pyc file.
Read more

module - Prestashop displayPaymentReturn hook url -

i new prestashop developer , trying create paymentmodule. have got show payment method can not proceed purchase because not know hot works. does know should redirect run hooddisplaypaymentreturn method? i happy if explain me complete navigation map make purchase. anyway, can find relation between hooks , pages? when have new module payment rely on simplest provided prestashop: bankwire. inside can find 3 hooks. hookpayment: public function hookpayment($params) { if (!$this->active) return; if (!$this->checkcurrency($params['cart'])) return; $this->smarty->assign(array( 'this_path' => $this->_path, 'this_path_bw' => $this->_path, 'this_path_ssl' => tools::getshopdomainssl(true, true).__ps_base_uri__.'modules/'.$this->name.'/' )); return $this->display(__file__, 'payment.tpl'); } hookdisplaypaymenteu: public fun
Read more