Hi can anyone help me for this. What im trying to do is to rank my session id but it seems not to work as it returns me just “1”. The third row should be 2, the row 5 should have 2 for rank and the row 6 should have 3 for rank and so on. I try to enumerate
Tag: google-bigquery
SQL: Remove part of string that is in another column of the table using REGEXP_REPLACE
I have a column that looks like so, but with more rows: Now, I am trying to remove the cat name from the message on each row in the table, so that I can see what the messages sent to each cat are, and which cats got the same message (i.e. this way I would be able to see that
fetch key value pairs from array objects in sql BigQuery
I need to parse the column mapping and fetch the key value pairs from the second object in array. I would like to create new columns with the fetched data. The data looks like this: The desired output: Answer Below is one of the approaches (BigQuery Standard SQL) if to apply above to sample data from your que…
BigQuery get row above empty column
Data: What would be the best way to get the row right above the empty cell? For example, I’d like to filter out row 1 and 3 based on the fact that column_b in row 2 and 4 are empty (in this example). A simple where statement does not work unfortunately, but how would it be possible to get the
replace value with 1 and apply SUM method in one query SQL
I need to replace the value in column imp from 1-0 to 1 and sum it up. Column imp is a STRING, so it also needs to be converted to INT. Each line represents the record so I need to sum up imp and group by another column (in order to get the number of records for a specific Advertiser)
How to join two tables by dependent match keys in BigQuery?
I have two tables in BigQuery First one is a list of rates. Rates have default values with source equal -1 for each combo code – offer. Apart from combo code – offer, some rates have specified source Second table has same columns as first table except rates + any other data. My goal join rates by …
Convert day of year & year to date Big Query
I have some data which instead of having a date field, contains the day of the year as a number and the year as a number in different columns. I have tried using big query functionality PARSE_DATE to achieve this by using PARSE_DATE(“%Y %j”, “2020 258”) but this does not work. When che…
BigQuery producing negative (!) zero when numeric 0.0 multiplied with negative numerics/integers
I intend to do a CSV export directly from BigQuery, using the bq CLI (with flag –format=csv). I run into an issue where if a column with a numeric value is multiplied with a negative one, BigQuery returns -0.0 (whatever that means) and this causes issues in the importing system of this CSV file. Example…
Parse out Y-M-D from Y-M-D H-M-S UTC sql bigquery
I need to parse out ‘%Y%m%d’ from the column in BigQuery. My data looks like this: I have tried the following: The error message: No matching signature for function PARSE_DATE for argument types: STRING, TIMESTAMP. Supported signature: PARSE_DATE(STRING, STRING) Desired output: 2000-09-25 Answer W…
BigQuery SQL conditional IN
I have been trying to construct a conditional IN like below, but this gives me the scalar subquery produced more than one element error. How should I approach this type of query instead? The tables are not related. Answer Try this below- As your other tables are not related, you can try this below logic-