BigQuery - counting number of events within a sliding time frame -

-

i count number of events within sliding time frame.

for example, know how many bids in last 1000 seconds google stock (goog).

i'm trying following query:

select    symbol,   start_date,   start_time,   bid_price,   count(if(max(start_time)-start_time<1000,1,null)) on (partition symbol order start_time asc) cnt [bigquery-samples:nasdaq_stock_quotes.quotes]   symbol = 'goog'

the logic follow: partition window (by symbol) ordered bid time (leaving alone bid date sake of simplicity). each window (defined row @ "head" of window) count number of rows have start_time less 1000 seconds "head" row time.

i'm trying use max(start_time) top row in window. doesn't seem work , error:

error: max analytic function , must accompanied on clause.

is possible have 2 analytic functions in 1 column (both count , max in case)? there different solution problem presented?

try using range function. 

select    symbol,    start_date,    start_time,    bid_price,    count(market_center) on (partition symbol order start_time range 1000 preceding) cnt   [bigquery-samples:nasdaq_stock_quotes.quotes]    symbol = 'goog'   order 2, 3

i used market_center counter, additional fields can used well.

note: range function not documented in bigquery query reference, it's standard sql function appears work in case


Comments

Post a Comment

Popular posts from this blog

java - Oracle EBS .ClassNotFoundException: oracle.apps.fnd.formsClient.FormsLauncher.class ERROR -

-
i need in configuring java on machine because, when try loading forms, loader goes in circles , gives error: java.lang.classnotfoundexception: oracle.apps.fnd.formsclient.formslauncher.class @ sun.plugin2.applet.applet2classloader.findclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass0(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass(unknown source) @ java.lang.classloader.loadclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadcode(unknown source) @ sun.plugin2.applet.plugin2manager.createapplet(unknown source) @ sun.plugin2.applet.plugin2manager$appletexecutionrunnable.run (unknown source) @ java.lang.thread.run(unknown source) i have tried re-installing firefox, older versions of java runtime environments, disabling proxy. what missing? application working on other machines. i spent around 100 hours finding s...
Read more

c# - how to use buttonedit in devexpress gridcontrol -

-
Image
i trying create gridcontrol buttonedit in 1 of columns. when user clicks button edit, popups form select product. when selection done on popup, returns datarow selection main grid below. but when column loses focus, value wrote column disappear. here code creates gridcontrol's data , buttonedit's click event. private void frmsiparisnew_load(object sender, eventargs e) { dt = new datatable(); dt.columns.add("malzeme_kodu",typeof(string)); dt.columns.add("malzeme_aciklama", typeof(string)); dt.columns.add("adet", typeof(decimal)); dt.columns.add("birim", typeof(string)); dt.columns.add("fiyat", typeof(decimal)); dt.columns.add("kur", typeof(string)); dt.columns.add("tutar", typeof(decimal)); datarow dr = dt.newrow(); dt.rows.add(dr); gc.datasource = dt; } private void repositoryitembuttoneditmal...
Read more

c# - System.FormatException' occurred in MongoDB.Bson.dll - XXX is not a valid 24 digit hex string -

-
i have created c# class this: public class employee { [bsonrepresentation(bsontype.objectid)] public string name { get; set; } public int age { get; set; } public list<string> address { get; set; } } when try save information (using mongodb) this: var e = new employee(); e.address = new list<string>(); e.address.add("address 1"); e.address.add("address 2"); e.age = 333; e.name = "some name"; context.employees.insert(e); i getting following error: an unhandled exception of type 'system.formatexception' occurred in mongodb.bson.dll additional information: 'some name' not valid 24 digit hex string. how can make string field act objectid in mongodb? reading docs: ... in case serializer convert objectid string when reading data database , convert string objectid when writing data database ( the string value must valid objectid )...
Read more