Converting python script logic to SQL redshift logic

Saikrishna Suresh 0
Viewed 16

I have a python script that is comparing a column with value type comma separated list with other comma separated lists using the if all logic.

In the below script, the table name is df1 and the data is structured like this:

enter image description here

list1 = 'DEARBORN', 'KENTUCKY','KANSAS CITY'
list2 = 'DEARBORN', 'KENTUCKY'
list2 = 'DEARBORN'
for ind in df:
    x = df['City Names'][ind]
    if all(elem in x for elem in list1):
    df['#_of_target_cities'][ind] = 3
    elif all(elem in x for elem in list2):
    df['#_of_target_cities'][ind] = 2
    elif all(elem in x for elem in list3):
    df['#_of_target_cities'][ind] = 2

Based on the value of City Names for each row, the for loop is assigning a value to the blank column in the event that the value contains all items from list 1 or list 2 or list 3. Final table would look something like this:

enter image description here

I am not familiar with SQL if statements or for loops, so I am not sure how to do this same operation in the SQL syntax.

list sql python
1 Answers

First off done think in terms of loops. SQL logic is based more on the concept of combinations and filters. So for this case the easiest way to think about performing this is finding all combinations where one of your target cities is in the city list. This is done with JOIN.

You will want your data and your target cities in table format. You likely have your data in a table but may need to make the target cities look like a table. You can use a WITH clause to create a table-like structure.

with target_cities as (
select 'DEARBORN' as tcity
UNION ALL select 'KENTUCKY'
UNION ALL select 'KANSAS CITY'
)

Next part of the query is to find all the above described combinations

combos as (
select index, city_names, tcity
from city_table c
join target_cities t
on c.city_names like '%' || t.tcity || '%'
)

Finally we just need to count the number of times each "city_name" shows up in the combination list.

select index, city_names, count(*) as no_of_target_cities
from combos
group by 1, 2;

Putting this all together into a test case you can try:

create table city_table (
  index int,
  city_names varchar(50)
  );
  
insert into city_table values
(0, 'city1,city3,city4,city5,city6'),
(1, 'city2,city4,city5,city6'),
(2, 'city1,city2,city3,city4,city5,city6');

with target_cities as (
  select 'city1' as tcity
  UNION ALL select 'city2'
  UNION ALL select 'city3'
),
combos as (
  select index, city_names, tcity
  from city_table c
  join target_cities t
  on c.city_names like '%' || t.tcity || '%'
)
select index, city_names, count(*) as no_of_target_cities
from combos
group by 1, 2
order by index;
Bill Weiner0
Related