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