回答編集履歴
1
文法の修正
answer
CHANGED
|
@@ -1,8 +1,13 @@
|
|
|
1
|
-
不格好ですが、以下のように
|
|
1
|
+
不格好ですが、以下のようにすることで解決できました。
|
|
2
2
|
```
|
|
3
|
+
SELECT
|
|
4
|
+
REPLACE(REPLACE(page_title, '」の検索結果一覧', ''), '「', '') AS page_title,
|
|
5
|
+
SUM(CASE WHEN action='view' THEN 1 ELSE 0 END) AS views
|
|
6
|
+
FROM ingestly.logs
|
|
7
|
+
WHERE url_pathname like '/***%'
|
|
3
8
|
AND page_title like '%の検索結果一覧'
|
|
9
|
+
AND page_title like CASE WHEN '{{page_title}}' = 'ALL' THEN page_title ELSE '%{{page_title}}%' END
|
|
10
|
+
AND CAST(FORMAT_TIMESTAMP("%Y-%m-%d", timestamp, "Asia/Tokyo") AS DATE) > DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 30 DAY)
|
|
4
|
-
|
|
11
|
+
group by page_title
|
|
5
|
-
CASE WHEN '{{page_title}}' = 'ALL' THEN page_title
|
|
6
|
-
|
|
12
|
+
ORDER BY views desc
|
|
7
|
-
END
|
|
8
13
|
```
|