When to and Not to use alias for JOINs in BigQuery

See also questions close to this topic

• Why does this Sql query shows the possible changes but does not implement it?

  So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

  SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

  Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.
• how to get weeklytotal and yesterday record in mysql in one table

  Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

  Table name-dailydata-

  Sample data

  employee_id	date	total
  20	2022-04-25	10
  20	2022-04-26	20
  20	2022-04-27	20
  20	2022-04-28	10
  20	2022-04-29	20
  20	2022-04-30	30
  20	2022-04-31	40
  20	2022-05-01	50
  40	2022-04-26	20

  expected output

  employee_id	weekly_total	yesterday_record
  20	200	40
  40	20	0

  mysql query to get weekly data

  select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  
• Procedure to display the Employees of a specific Department

  Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

  Name of the argument as Input : DeptNo

  Write the code to create the procedure

  Schema

  I wrote the Query like this but i didnt get expected output

  CREATE  PROCEDURE Employeesdept(@Deptno varchar)
  AS
  Begin
  SELECT lastname as name
  from employee
  where workdept= 'D21'
  end
  

  Expected output:

  Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------
• Join to another table only matching specific records

  I have a table of ports:

  drop table if exists ports;
  create table ports(id int, name char(20));
  
  insert into ports (id, name ) values
    (1, 'Port hedland'), 
    (2, 'Kwinana');
  

  And a table of tariffs connected to those ports:

  drop table if exists tariffs;
  create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11));
  
  insert into tariffs (id, portId, price, expiry ) values
      (1, 2, 11.00, 1648408400), 
      (2, 2, 12.00, 1648508400), 
      (3, 2, 13.00, 1648594800), 
      (4, 2, 14.00, 1651273200),
      (5, 2, 15.00, 2250000000 );
  
  insert into tariffs (id, portId, price, expiry ) values
      (1, 1, 21.00, 1648408400), 
      (2, 1, 22.00, 1648508400), 
      (3, 1, 23.00, 1648594800), 
      (4, 1, 24.00, 1651273200),
      (5, 1, 25.00, 2250000000 );
  

  Each tariff has an expiry.

  I can easily make a query to figure out the right tariff for as specific date for each port. For example at timestamp 1648594700 the right tariff is:

  SELECT * FROM tariffs 
  WHERE 1648594700 < expiry AND portId = 2
  ORDER BY expiry
  LIMIT 1
  

  Result:

  id  portId  price   expiry
  3   2   13.00   1648594800
  

  However, in my application I want to be able to pull in the right tariff starting from the ports record.

  For one record, I can do this:

  SELECT * FROM ports 
  LEFT JOIN tariffs on tariffs.portId = ports.id 
  WHERE 1648594700 < tariffs.expiry AND ports.id = 2
  LIMIT 1
  

  Result:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  

  This feels a little 'dirty', especially because I am doing a lookup on a record, and then forcing only one result using LIMIT. But, OK.

  What I cannot do, and can't work out how to do, is a query that will return a list of ports, and each port having a price field that matches the constraint above (that is, the record with the highest expiry compared to 1648594700 for each port).

  This obviously won't work:

  SELECT * FROM ports 
  left join tariffs on tariffs.portId = ports.id
  where 1648594700 < tariffs.expiry
  

  Since the result of the query, testing with timestamp 1648594700, would be:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  2   Kwinana 4   2   14.00   1651273200
  2   Kwinana 5   2   15.00   2250000000
  1   Port he 3   1   23.00   1648594800
  1   Port he 4   1   24.00   1651273200
  1   Port he 5   1   25.00   2250000000
  

  Instead, the result for all ports (before further filtering) should be:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  1   Port he 3   1   23.00   1648594800
  

  Is there a clean, non-hacky way to have such a result? As an added constraint, is this possible for this to be done in ONE query, without temp tables etc.?
• how to apply a text string in a variable with rename function

  When using the PHP rename function I need to use text in a variable in the rename, but I can only get the rename to work when I manually enter the text, for example

  If I use this

  $original_file_name = '/my_file_path/Genesis _ Mad Man Moon _ A Trick Of The Tail (2007 Remaster) HQ Audio (128 kbps).mp3'; 
  

  in rename like this

  rename($filePath, $original_file_name); 
  

  it works

  But if I do this

  $a = "'";
  $b = '/my_file_path/';
  $c = $fin_name;
  $original_file_name = $a.$b.$c.$a;
  

  I get this

  '/home2/ricstrav/domains/nerjabible.com/public_html/plupload_latest/Genesis _ Mad Man Moon _ A Trick Of The Tail (2007 Remaster) HQ Audio (128 kbps).mp3
  

  ' with the end quote spaced two lines down from the end of the line so it does not rename
• Combine Multiple CTE

  I have multiple CTEs that I would like to combine in a particular format using PostgreSQL. See below sample tables. I have data associated with multiple materials that I would like to combine with data and material info of a product table into a final table.

  Product Table:

  workorder	data	material1-1	material1-2	material2-1	material2-2
  WO1	values	WO2	WO3	WO4	WO5

  +

  Material1 Table:

  workorder	data
  WO2	values2
  WO3	values3

  +

  Material2 Table:

  workorder	data
  WO4	values4
  WO5	values5

  =

  Final Table:

  workorder	data	material1-1	data1-1	material1-2	data1-2	material2-1	data2-1	material2-2	data2-2
  WO1	values	WO2	values2	WO3	values3	WO4	values4	WO5	values5

  Can this be done with joins?
• How to cleanup BigQuery export to CSV using pandas

  I exported my BigQuery data to CSV but can't figure out how to clean up the data as the headers are all appended on the backend in the same row.

  Here's my code:

  from google.cloud import bigquery
  import pandas as pd
  
  project = 'project1'
  client = bigquery.Client(project=project)
  
  defineQuery = """
      SELECT *  
      FROM table
      LIMIT 5;    
  """
  
  df = pd.DataFrame(client.query(defineQuery)) 
  df.to_csv(r'C:/file-name.csv') 
  

  The BigQuery CSV file export came out with just 2 columns like the following:-

  |     | 0     |
  |-----|-------|
  |    1|Row((123,'data','data2'), {'Field 1':0, 'Field 2':1, 'Field 3':2})
  |    2|Row((123,'data','data2'), {'Field 1':0, 'Field 2':1, 'Field 3':2})
  |    3|Row((123,'data','data2'), {'Field 1':0, 'Field 2':1, 'Field 3':2})
  |    4|Row((123,'data','data2'), {'Field 1':0, 'Field 2':1, 'Field 3':2})
  |    5|Row((123,'data','data2'), {'Field 1':0, 'Field 2':1, 'Field 3':2})
  

  How do I clean it up so it's like the following?

  |     | Field 1 | Field 2 | Field 3 |
  |-----|---------|---------|---------|
  |    1| 123     | data    | data2   |
  |    2| 123     | data    | data2   |
  |    3| 123     | data    | data2   |
  |    4| 123     | data    | data2   |
  |    5| 123     | data    | data2   |
  

  Thanks in advance!
• How to consecutively count everything greater than or equal to itself in SQL?

  Let's say if I have a table that contains Equipment IDs of equipments for each Equipment Type and Equipment Age, how can I do a Count Distinct of Equipment IDs that have at least that Equipment Age.

  For example, let's say this is all the data we have:

  equipment_type	equipment_id	equipment_age
  Screwdriver	A123	1
  Screwdriver	A234	2
  Screwdriver	A345	2
  Screwdriver	A456	2
  Screwdriver	A567	3

  I would like the output to be:

  equipment_type	equipment_age	count_of_equipment_at_least_this_age
  Screwdriver	1	5
  Screwdriver	2	4
  Screwdriver	3	1

  Reason is there are 5 screwdrivers that are at least 1 day old, 4 screwdrivers at least 2 days old and only 1 screwdriver at least 3 days old.

  So far I was only able to do count of equipments that falls within each equipment_age (like this query shown below), but not "at least that equipment_age".

  SELECT
  equipment_type,
  equipment_age,
  COUNT(DISTINCT equipment_id) as count_of_equipments
  FROM equipment_table
  GROUP BY 1, 2
  
• Querying from BigQuery to Python

  I'm trying to query data to bigquery with this code as mentioned in google docs:

  from google.cloud import bigquery
  client = bigquery.Client()
  tab = client.query(""" SELECT * FROM EXTERNAL_QUERY("projects/projectid/locations/europe-west6/connections/system", "SELECT * FROM table") """)
  return render_template('index.html', tab=tab.result())
  

  but I'm getting this error:

  ValueError: ('Iterator has already started', <google.cloud.bigquery.table.RowIterator object at 0x7f86c1ef0160>)
  
• Struggling to set multiple aliases in Powershell, VSCODE

  When I try to set an alias where I will type w++17 it should correspond to w++17 -g++ main.cpp -Wall -Wextra -pedantic -Weffc++ -Wold-style-cast -Woverloaded-virtual -fmax-errors=3

  I do it in Poweshell terminal using VSCODE. When I try to set the alias I do:

  Set-Alias -Name w++17 -Value g++ main.cpp -Wall -Wextra -pedantic -Weffc++ -Wold-style-cast -Woverloaded-virtual -fmax-errors=3
  

  I get the following error:

  Set-Alias : A positional parameter cannot be found that accepts argument 'main.cpp'.
  At line:1 char:1
  + Set-Alias -Name w++17 -Value g++ main.cpp -Wall -Wextra -pedantic -We ...
  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : InvalidArgument: (:) [Set-Alias], ParameterBindingException
      + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.SetAliasCommand
  

  It looks like I cant set an alias containing multiple values/arguments?
• Declaring alias types for a tuple of fixed or arbitrary size and element types

  Edit: Initial question was simple to answer due to a typo in the code, so I updated the question to make it a bit more exotic

  Alias types for different views of tuples

  How to define an alias type for a tuple with any of the following combination of features:

  • fixed size vs. arbitrary size
  • elements of a fixed type (say Int64} vs. qualifying the type of the element to be a parameter of the alias type

  Here is my attempt. Given the following hint

  julia> typeof((1,2,3,4))
  NTuple{4, Int64}
  

  I could define an alias type for a fixed size fixed type tuple as follows:

  julia> const Tuple4DInt64 = NTuple{4,Int64}
  

  If the type of the elements need to be qualified, then the above code can be modified to

  julia> Tuple4D = NTuple{4,T} where T 
  

  This is equivalent to

  julia> Tuple4D = NTuple{4,T} where T <: Any
  

  which can be used as follows:

  julia> Tuple4D
  NTuple{4, T} where T
  
  julia> Tuple4D{Int64} 
  NTuple{4, Int64} 
  

  One can ensure the alias type to be a tuple of fixed size but with various arbitrary types of elements as follows

  julia> Tuple4D{Any}
  NTuple{4, Any}
  

  Qualifying the size of the tuple as a parameter for the type alias is intuitive as well:

  julia> TupleNDInt64 = NTuple{N,Int64} where N
  Tuple{Vararg{Int64, N}} 
  
  julia> TupleNDInt64{4} 
  NTupleNDInt64{4, Int64}
  

  Last case, qualifying both the type and the size as parameters for the alias type, here I am not able to get it right.