ãã®ãšã©ãŒããããã°ããŠ
以äžã®ãšã©ãŒãPythonã¢ããªã±ãŒã·ã§ã³ã§çºçããŠããŸããåå ãç¹å®ããŠä¿®æ£ããŠãã ããã
Traceback (most recent call last):
File "app.py", line 23, in get_user_orders
total = sum(order["price"] * order["quantity"] for order in orders)
TypeError: unsupportâŠ
Gemini 3.7 Flash
çºçããŠãããšã©ãŒã®åå ãšä¿®æ£æ¹æ³ã¯ä»¥äžã®éãã§ãã
---
### åå
ããŒã¿ããŒã¹ããååŸãã `order["price"]` ã®å€ã **æååå (`str`)** ã«ãªã£ãŠããããã§ãã
Pythonã§ã¯æåååãšæ°å€åã®æãç®ïŒæ°å€èšç®ïŒãã§ããªãããã`TypeError: unsupported operand type(s) for *: 'str' and 'int'` ãçºçããŠããŸãã
---
### ä¿®æ£æ¹æ³
çšéïŒéè²šã®æ±ãïŒã«å¿ããŠä»¥äžã®ããããã®æ¹æ³ã§ä¿®æ£ããŸãã
#### ãã¿ãŒã³1: `Decimal` ã䜿ãïŒæšå¥šïŒéé¡èšç®ã§äžžã誀差ãé²ãå ŽåïŒ
éèãECã®éé¡èšç®ã§ã¯ãæµ®åå°æ°ç¹æ°ã®èª€å·®ãé¿ãããã `decimal.Decimal` ã䜿ãã®ãå®å
šã§ãã
```python
from decimal import Decimal
def get_user_orders(user_id):
orders = db.query("SELECT price, quantity FROM orders WHERE user_id = %s", user_id)
# price ã Decimal ã«ãquantity ã int ã«å€æããŠèšç®
total = sum(Decimal(str(order["price"])) * int(order["quantity"]) for order in orders)
return {"user_id": user_id, "total":âŠ
å¿çæé 8.4ç§åºå 1039 ããŒã¯ã³
Qwen3.8 Max
åå ã¯ã`order["price"]` ãæ°å€ã§ã¯ãªãæååã«ãªã£ãŠããããšã§ãã
ãšã©ãŒå
容ã®
```text
TypeError: unsupported operand type(s) for *: 'str' and 'int'
```
ã¯ã次ã®ããã«ãæåå Ã æŽæ°ããèšç®ããããšãããšãã«åºãŸãã
```python
"1000" * 2
```
æ¬æ¥ã¯æ°å€ãšããŠæãç®ãããã®ã«ãDB ããååŸãã `price` ãæåååã«ãªã£ãŠãããã倱æããŠããŸãã
---
## èããããåå
äŸãã°æ¬¡ã®ãããªã±ãŒã¹ã§ãã
1. `orders.price` ã«ã©ã ã `TEXT` / `VARCHAR` åã«ãªã£ãŠãã
2. DB ãã©ã€ããäŸ¡æ ŒãæååãšããŠè¿ããŠãã
3. ããŒã¿ãšã㊠`"1000"` ã®ããã«æååä¿åãããŠãã
4. SQL åŽã§æååãšããŠååŸããŠããŸã£ãŠãã
çŸåšã®ã³ãŒãã§ã¯ã`price` ãæååã®ãŸãŸæãç®ã«äœ¿ãããŠããŸãã
```python
total = sum(order["price"] * order["quantity"] for order in orders)
```
---
## ç°¡åãªä¿®æ£
æäœéã®ä¿®æ£ãšããŠã¯ã`price` ãæ°å€ã«å€æããŸãã
```python
def get_user_orders(user_id):
orders = db.query(
"SELECT price, quantity FROM orders WHERE userâŠ
å¿çæé 68.2ç§åºå 2983 ããŒã¯ã³
SQLã¯ãšãªã®æé©å
以äžã®SQLã¯ãšãªã¯500äžè¡ã®ããŒãã«ã«å¯ŸããŠå®è¡ãããš30ç§ä»¥äžããããŸããæé©åããŠãã ããã
SELECT
p.product_name,
c.category_name,
COUNT(o.order_id) as order_count,
SUM(o.amount) as total_sales,
AVG(r.rating) as avg_rating
FROM products p
LEFT JOINâŠ
Gemini 3.7 Flash
ãã®ã¯ãšãªãé
ãïŒããã³**éèšçµæã誀ã£ãŠãã**ïŒäž»ãªåå ã¯ã**ãçµåã«ããè¡ã®ççºïŒãã«ã«ãç©ïŒã**ãš**ãé©åãªã€ã³ããã¯ã¹ã®äžè¶³ã**ã§ãã
---
### äž»ãªåé¡ç¹
1. **éèšã®äžæŽåãšäžéããŒã¿ã®ççºïŒæå€§ã®ããã«ããã¯ïŒ**
- 1ã€ã®ååã«å¯ŸããŠã泚æïŒ`order_details`ïŒããšãã¬ãã¥ãŒïŒ`reviews`ïŒããåæã«çµåããŠããããã**è¡æ°ããæ³šææ° à ã¬ãã¥ãŒæ°ãã«èšãäžãããŸã**ã
- äŸ: 泚æ100ä»¶ãã¬ãã¥ãŒ10ä»¶ã®ååãããå Žåãäžéè¡ã¯1,000è¡ã«ãªãã`SUM(o.amount)` ã¯æ£ãã売äžã®10åã«èšç®ãããŠããŸããŸãããŸãã500äžè¡èŠæš¡ã§ã¯æ°åäžãæ°åè¡ã®äžéããŒãã«ãã¡ã¢ãªäžã«çæãããåçã«é
ããªããŸãã
2. **LEFT JOIN ã INNER JOIN åããŠãã**
- `WHERE o.created_at >= '2024-04-01'` ã«ããã売äžããªãååïŒ`o.created_at` ã NULL ã®è¡ïŒã¯é€å€ãããŸãã
3. **ã€ã³ããã¯ã¹ã®æ¬ åŠ**
- çµåããŒïŒ`product_id`, `category_id`ïŒãçµãèŸŒã¿æ¡ä»¶ïŒ`is_active`, `status`, `created_at`ïŒã«ã€ã³ããã¯ã¹ããªããããå
šããŒãã«ã§ãã«ã¹ãã£ã³ãçºçããŠããŸãã
---
### æé©åãããSQL
泚æããŒã¿ãšã¬ãã¥ãŒããŒã¿ã**äºåã«ãµãã¯ãšãªïŒCTEïŒã§ååããšã«éèš**ããŠããçµåããŸããããã«ãããè¡ã®ççºãé²ããèšç®âŠ
å¿çæé 13.4ç§åºå 1924 ããŒã¯ã³
Qwen3.8 Max
以äžã¯ã500äžè¡èŠæš¡ã§30ç§ä»¥äžããããå Žåã«ããŸãçãã¹ããã€ã³ããšæ¹åæ¡ã§ãã
ãã®ã¯ãšãªã¯ **ã€ã³ããã¯ã¹è¿œå ã ãã§ãªããã¯ãšãªæ§é èªäœã«å€§ããªåé¡** ããããŸãã
---
## 1. 倧ããªåé¡ïŒLEFT JOIN ã«ãã£ãŠéèšè¡æ°ãççºããŠãã
çŸåšã®ã¯ãšãªã¯ã`products` ã«å¯Ÿã㊠`order_details` ãš `reviews` ãäž¡æ¹ `LEFT JOIN` ããŠããŸãã
```sql
LEFT JOIN order_details od ON od.product_id = p.id
LEFT JOIN orders o ON o.order_id = od.order_id
LEFT JOIN reviews r ON r.product_id = p.id
```
ããã«ãããäŸãã°ããååã«ä»¥äžãããå Žåã
- 泚ææçްïŒ100è¡
- ã¬ãã¥ãŒïŒ200è¡
`100 à 200 = 20,000è¡` ã«èšãã¿ãŸãã
ãã®çµæã
```sql
COUNT(o.order_id)
SUM(o.amount)
```
ã **ã¬ãã¥ãŒè¡æ°åã ãéè€å ç®** ãããå¯èœæ§ããããŸãã
ã€ãŸãããã®ã¯ãšãªã¯é
ãã ãã§ãªããçµæãæ£ãããªãå¯èœæ§ããããŸãã
---
## 2. æ¹åæ¹éïŒéèšãåé¢ãã
泚æéé¡ã»æ³šææ°ã®éèšãšãã¬ãã¥ãŒå¹³åã¯å¥ã
ã«éèšããŠããçµåãã¹ãã§ãã
### æ¹ååŸã®ã¯ãšãªäŸ
```sql
WITH order_agg AS (
SELECT
od.producâŠ
å¿çæé 106.3ç§åºå 5133 ããŒã¯ã³
ããžãã¹ã¡ãŒã«äœæ
ååŒå
ã®ç°äžéšé·ã«ãçŽåäºå®ã®è£œåã«äžå
·åãèŠã€ãããçŽæã1é±éå»¶æããå¿
èŠãããæšãäŒããããžãã¹ã¡ãŒã«ãäœæããŠãã ããã
æ¡ä»¶ïŒ
- åå ã¯åè³ªæ€æ»ã§è»œåŸ®ãªäžå
·åãçºèŠããããã
- ä»£æ¿æ¡ãšããŠæ«å®çãå
ã«çŽåããããšãå¯èœ
- æ¹ããŠãè©«ã³ã®èšªåãããã
- æ¥æ¬ã®ããžãã¹ãããŒã«æ²¿ã£ãäžå¯§ãªæé¢ã«ããããš
Gemini 3.7 Flash
ååŒå
ã®ç°äžéšé·ãžéããçŽæå»¶æã®ãè©«ã³ãšãçžè«ã®ããžãã¹ã¡ãŒã«æé¢ãäœæããŸããã
ç¶æ³ã«åãã㊠[ ] ã®éšåãæžãæããŠã䜿çšãã ããã
---
**ä»¶åïŒãéèŠã»ãè©«ã³ã[補åå]ã®çŽåæ¥å»¶æã®ãé¡ããšãçžè«**
**æ¬æïŒ**
[ååŒå
äŒç€Ÿå]
[éšçœ²å] éšé·
ç°äž [æ§ / åå]
å¹³çŽ ã¯æ Œå¥ã®ãé«é
ãè³ããåã埡瀌ç³ãäžããŸãã
[èªç€Ÿå]ã®[èªåã®æ°å]ã§ããããŸãã
ãã®åºŠã¯ããæãæ¥ã«çŽåãäºå®ããŠãããŸãã[補åå]ãã«ã€ããŸããŠã倧å€éèŠãªãè©«ã³ãšãçžè«ãããããŸãã
åºè·åã®æçµåè³ªæ€æ»ã«ãããŸããŠã補åã®äžéšã«è»œåŸ®ãªäžå
·åã確èªãããŸããã
å®çšäžã¯åé¡ãªãç¯å²ã®ãã®ã§ã¯ããããŸããã貎瀟ã«äžå
šã®ç¶æ
ã§ãå±ãããããã該åœç®æã®æ¹ä¿®ããã³å床ã®åäœæ€èšŒãè¡ãããåããŸãã
ã€ããŸããŠã¯ã倧å€å¿èŠãããé¡ãã§ã¯ããããŸãããåœåã®äºå®ãã**ã1é±éã**çŽæããå»¶æããã ãããããé£çµ¡å·®ãäžããæ¬¡ç¬¬ã§ãã
ã**ã倿ŽåŸã®çŽåäºå®æ¥ãïŒãæãæ¥ïŒãïŒ**
è²Žç€Ÿã®æ¥åããã³ã¹ã±ãžã¥ãŒã«ã«å€å€§ãªããè¿·æããããããããšããæ·±ããè©«ã³ç³ãäžããŸãã
ãªãã貎瀟æ¥åãžã®åœ±é¿ãæå°éã«æããããã®ä»£æ¿æ¡ãšããããŸããŠãçŸåšåé¡ãªãåäœããŠãã**ãæ«å®çããå
è¡ããŠåœåã®äºå®æ¥ïŒãæãæ¥ïŒã«çŽåãããŠããã ãããšãå¯èœ**ã§ããããŸãããã®å Žåãæ¹ä¿®å®äºçãå®ææ¬¡ç¬¬ãéããã«å·®ãæ¿ã察å¿ãããããŸãã
é²ãæ¹ãä»£æ¿æ¡ã®å¯åŠã«ã€ããŸããŠãç°äžæ§ã®ãéœåã䌺ããªãããçžè«ãããŠããã ããŸããšå¹žãã§ãã
ãŸããæ¬ä»¶ã«é¢ããŸããŠãçŽæ¥ã䌺âŠ
å¿çæé 7.8ç§åºå 920 ããŒã¯ã³
Qwen3.8 Max
以äžã¯ãååŒå
ã®ç°äžéšé·å®ã«éãããžãã¹ã¡ãŒã«æäŸã§ããç¶æ³ã«å¿ããŠã ãéšåã調æŽããŠãã ããã
---
ä»¶åïŒçŽåäºå®è£œåã®äžå
·åã«äŒŽãçŽæå€æŽã®ãé¡ã
æ ªåŒäŒç€ŸãååŒå
åã
ç°äžéšé·
å¹³çŽ ãã倧å€ãäžè©±ã«ãªã£ãŠãããŸãã
æ ªåŒäŒç€Ÿãèªç€Ÿåãã®ãæ°åãã§ããããŸãã
ãã®ãã³ã¯ãã補ååãã®çŽåã«ã€ããŸããŠããé£çµ¡ç³ãäžããŸããã
çŸåšãçŽåã«åããŠæçµç¢ºèªãé²ããŠãããŸãããã瀟å
ã®åè³ªæ€æ»ã«ãããŸããŠã軜埮ãªäžå
·åãçºèŠãããŸããã
ã客æ§ãžå®å¿ããŠãå©çšããã ããå質ã確ä¿ãããããä¿®æ£ããã³å確èªã«äžå®ã®æéãèŠããèŠèŸŒã¿ã§ããããŸãã
ã€ããŸããŠã¯ãå€§å€æçž®ã§ã¯ããããŸãããçŸåšã®çŽåäºå®ã1é±éå»¶æãããŠããã ãããããé¡ãç³ãäžããŸãã
åœåã®äºå®ã©ãããå±ãã§ãããç°äžéšé·ãã¯ãã貎瀟ã®çæ§ã«ãè¿·æããããããŸãããšãå¿ãããè©«ã³ç³ãäžããŸãã
ãªããããæ¥ãã§ã確èªããã ãå¿
èŠãããå Žåã¯ãä¿®æ£åã®æ«å®çãå
ã«ãçŽãããããšãå¯èœã§ãã
ãã®å Žåã¯ãæ£åŒãªä¿®æ£çãåŸæ¥ãããããŠçŽåãããŠããã ã圢ãšãªããŸãã
ãéœåã«åãããŠæè»ã«å¯Ÿå¿ããããŸãã®ã§ããåžæãããããŸããããç¥ããããã ããŸããšå¹žãã§ãã
æ¬æ¥ã§ããã°çŽæ¥ãè©«ã³ç³ãäžããã¹ããšããã§ã¯ããããŸããããŸãã¯ã¡ãŒã«ã«ãŠãé£çµ¡ç³ãäžããŸããã
åŸæ¥ãæ¹ããŠãè©«ã³ãšã説æã«ã䌺ããããåããŸãã®ã§ããéœåã®ããæ¥æããç¥ããããã ããŸããšå¹žãã§ãã
ãã®ãã³ã¯ãè¿·æããããããèª ã«ç³ãèš³ããããŸããã
äœåã容赊è³ããŸâŠ
å¿çæé 17.1ç§åºå 681 ããŒã¯ã³