admin管理员组文章数量:1320822
Given this table containing buying information of customer_id "xxxxxx". How can we write a script that computes the column nb_bmw_cars that are to the number of rows where car_type is "BMW" BUT in the interval ] date - 365 days: date - 1 day [ for each date. (in sqlalchemy).
customer_id | date | car_type | nb_bmw_cars |
---|---|---|---|
xxxxxx | 2022-11-22 | BMW | - |
xxxxxx | 2023-08-24 | BMW | 1 |
xxxxxx | 2023-12-21 | BMW | 2 |
xxxxxx | 2023-12-21 | BMW | 2 |
xxxxxx | 2023-12-21 | MERCEDES | 2 |
xxxxxx | 2023-12-21 | BMW | 2 |
xxxxxx | 2023-12-21 | BMW | 2 |
xxxxxx | 2024-02-07 | BMW | 6 |
xxxxxx | 2024-02-07 | BMW | 6 |
xxxxxx | 2024-02-07 | BMW | 6 |
xxxxxx | 2024-10-31 | BMW | 9 |
xxxxxx | 2024-10-31 | BMW | 9 |
xxxxxx | 2024-10-31 | BMW | 9 |
Given this table containing buying information of customer_id "xxxxxx". How can we write a script that computes the column nb_bmw_cars that are to the number of rows where car_type is "BMW" BUT in the interval ] date - 365 days: date - 1 day [ for each date. (in sqlalchemy).
customer_id | date | car_type | nb_bmw_cars |
---|---|---|---|
xxxxxx | 2022-11-22 | BMW | - |
xxxxxx | 2023-08-24 | BMW | 1 |
xxxxxx | 2023-12-21 | BMW | 2 |
xxxxxx | 2023-12-21 | BMW | 2 |
xxxxxx | 2023-12-21 | MERCEDES | 2 |
xxxxxx | 2023-12-21 | BMW | 2 |
xxxxxx | 2023-12-21 | BMW | 2 |
xxxxxx | 2024-02-07 | BMW | 6 |
xxxxxx | 2024-02-07 | BMW | 6 |
xxxxxx | 2024-02-07 | BMW | 6 |
xxxxxx | 2024-10-31 | BMW | 9 |
xxxxxx | 2024-10-31 | BMW | 9 |
xxxxxx | 2024-10-31 | BMW | 9 |
To understand what is nb_bmw_cars, if you take for example "2024-10-31", it has 9 because there is 9 transactions of car_type BMW before the date (2024-10-31) .
Share Improve this question edited Jan 18 at 7:17 redj asked Jan 17 at 22:01 redjredj 901 silver badge7 bronze badges 4 |2 Answers
Reset to default 1For the date 2024-10-31
, nb_bmw_cars
should be 7 instead of 9(as mentioned in question) since there are dates(2022-11-22
,2023-08-24
) older than
(2024-10-31) -1
and (2024-10-31) - 365
days which is the criteria mentioned in question.Same holds true for other dates as well.
To calculate the logic you can just self join the table with itself, where a date is between 365 days and -1 and filter the car_type as BMW
In case you want to calculate for different customer ids, you would need to join based on customer id.I have commented it here as in the sample data given it is not required as all customer ids are same.
select_query = text("""
select t1.customer_id, t1.date, t1.car_type,
(select COUNT(*) FROM test AS t2
--where t2.customer_id = t1.customer_id
where t2.date between date(t1.date, '-365 day') and date(t1.date, '-1 day')
and t2.car_type = 'BMW'
) as nb_bmw_cars
from test AS t1
""")
result = session.execute(select_query).fetchall()
Output
xxxxxx | 2022-11-22 | BMW | 0
xxxxxx | 2023-08-24 | BMW | 1
xxxxxx | 2023-12-21 | BMW | 1
xxxxxx | 2023-12-21 | BMW | 1
xxxxxx | 2023-12-21 | MERCEDES | 1
xxxxxx | 2023-12-21 | BMW | 1
xxxxxx | 2023-12-21 | BMW | 1
xxxxxx | 2024-02-07 | BMW | 5
xxxxxx | 2024-02-07 | BMW | 5
xxxxxx | 2024-02-07 | BMW | 5
xxxxxx | 2024-10-31 | BMW | 7
xxxxxx | 2024-10-31 | BMW | 7
xxxxxx | 2024-10-31 | BMW | 7
It is not clear whether you want to update or select, in case you wanted update here is an equivalent
update = text("""update test
set nb_bmw_cars = (select COUNT(*) from test AS t2
where t2.customer_id = test.customer_id
and t2.car_type = 'BMW'
and t2.date BETWEEN DATE(test.date, '-365 day') AND DATE(test.date, '-1 day'))""")
Conceptually it is the same, but here is the Sqlalchemy ORM version of it.
Sample data
data = [
{'customer_id': 'xxxxxx', 'date': date(2022, 11, 22), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2023, 8, 24), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2023, 12, 21), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2023, 12, 21), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2023, 12, 21), 'car_type': 'MERCEDES'},
{'customer_id': 'xxxxxx', 'date': date(2023, 12, 21), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2023, 12, 21), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2024, 2, 7), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2024, 2, 7), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2024, 2, 7), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2024, 10, 31), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2024, 10, 31), 'car_type': 'BMW'},
{'customer_id': 'xxxxxx', 'date': date(2024, 10, 31), 'car_type': 'BMW'}
]
-- connections etc
for each_row in all_rows:
interval_start = each_row.date - timedelta(days=365)
interval_end = each_row.date - timedelta(days=1)
count = connection.execute(
select(func.count()).where(and_(
test.columns.customer_id ==
each_row.customer_id,test.columns.car_type ==
'BMW',test.columns.date.between(interval_start,
interval_end)))).scalar()
connection.execute(
test.update().where(and_(
test.columns.customer_id == each_row.customer_id,
test.columns.date == each_row.date
)).values(nb_bmw_cars=count) )
results = connection.execute(select(test.columns.customer_id, test.columns.date, test.columns.car_type, test.columns.nb_bmw_cars)).fetchall()
for each in results:
print(each)
Output
('xxxxxx', datetime.date(2022, 11, 22), 'BMW', 0)
('xxxxxx', datetime.date(2023, 8, 24), 'BMW', 1)
('xxxxxx', datetime.date(2023, 12, 21), 'BMW', 1)
('xxxxxx', datetime.date(2023, 12, 21), 'BMW', 1)
('xxxxxx', datetime.date(2023, 12, 21), 'MERCEDES', 1)
('xxxxxx', datetime.date(2023, 12, 21), 'BMW', 1)
('xxxxxx', datetime.date(2023, 12, 21), 'BMW', 1)
('xxxxxx', datetime.date(2024, 2, 7), 'BMW', 5)
('xxxxxx', datetime.date(2024, 2, 7), 'BMW', 5)
('xxxxxx', datetime.date(2024, 2, 7), 'BMW', 5)
('xxxxxx', datetime.date(2024, 10, 31), 'BMW', 7)
('xxxxxx', datetime.date(2024, 10, 31), 'BMW', 7)
('xxxxxx', datetime.date(2024, 10, 31), 'BMW', 7)
Thanks for your answers, I post this one here in sqlalchemy. It worked for me it is based on a self join like @samhita mentioned in the comment above.
table_cars_alias_1 = aliased(table_cars)
table_cars_alias_2 = aliased(table_cars)
select_query = (
select(
table_cars_alias_1.c.customer_id,
table_cars_alias_1.c.date,
table_cars_alias_1.c.type_car,
func.sum(
case(
(table_cars_alias_2.c.type_car == 'BMW', 1),
else_=0
)
).label("nb_cars_bmw"),
func.sum(case(
(table_cars_alias_2.c.type_car == 'MERCEDES', 1)
,else_=0 )
).label("nb_cars_mercedes")
).select_from(
table_cars_alias_1.join(
table_cars_alias_2,
and_(
table_cars_alias_1.c.customer_id == table_cars_alias_2.c.customer_id,
table_cars_alias_2.c.date.between(
func.date(table_cars_alias_1.c.date) - text("interval '365 days'"),
func.date(table_cars_alias_1.c.date) - text("interval '1 days'")
)
)
)
)
.group_by(
table_cars_alias_1.c.customer_id,
table_cars_alias_1.c.date,
table_cars_alias_1.c.type_car,
)
)
本文标签: pythonbuiling new column in sqlalchemyStack Overflow
版权声明:本文标题:python - builing new column in sqlalchemy - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742089612a2420185.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
test_alias = aliased(test) query = select(test.c.customer_id, test.c.date, test.c.car_type, func.count().filter( and_( test_alias.c.date.between( test.c.date - timedelta(days=365), test.c.date - timedelta(days=1) ), test.c.car_type == 'BMW' ) ).label('nb_bmw_cars') ).outerjoin(test_alias, and_(test_alias.c.customer_id == test_alias.c.customer_id) ).group_by(test.c.customer_id, test.c.date, test.c.car_type)
– redj Commented Jan 18 at 9:39