select "_"."product_name_lower",
"_"."a0",
"_"."a1"
from
(
select "rows"."product_name_lower" as "product_name_lower",
sum("rows"."purchases") as "a0",
sum("rows"."purchase_revenue") as "a1"
from
(
select "_"."daterange" as "daterange",
"_"."product_name" as "product_name",
"_"."purchase_revenue" as "purchase_revenue",
"_"."purchases" as "purchases",
lower("_"."product_name") as "product_name_lower"
from
(
select "_"."daterange",
"_"."product_name",
"_"."purchase_revenue",
"_"."purchases"
from
(
select "daterange",
"product_name",
"purchase_revenue",
"purchases"
from "public"."cc_data_view" "$Table"
) "_"
where ("_"."daterange" < date '2024-01-01' and "_"."daterange" >= date '2023-01-01') and ("_"."product_name" in ('4G Cellular Trail Camera', '4K Wildlife Trail Camera', 'Wireless Trail Camera', '8-Person Cabin Tent', '20MP No-Glow Trail Camera', 'HD Wildlife Camera', '4-Season Mountaineering Tent', 'Trail Camera', '16MP Trail Camera with Solar Panel', '10-Person Family Tent'))
) "_"
) "rows"
group by "product_name_lower"
) "_"
where not "_"."a0" is null or not "_"."a1" is null
limit 1000001
SELECT LOWER(CAST(CAST("cc_data_view"."product_name" AS TEXT) AS TEXT)) AS "Calculation_1562467608097775616",
SUM("cc_data_view"."purchase_revenue") AS "sum:purchase_revenue:ok",
SUM("cc_data_view"."purchases") AS "sum:purchases:ok"
FROM "public"."cc_data_view" "cc_data_view"
WHERE (("cc_data_view"."daterange" >= (DATE '2023-01-01')) AND ("cc_data_view"."daterange" <= (DATE '2023-12-31')))
GROUP BY 1
HAVING ((SUM("cc_data_view"."purchase_revenue") >= 999999.99999998999) AND (SUM("cc_data_view"."purchase_revenue") <= 2000000.00000002))
SELECT
LOWER((cc_data_view."product_name")) AS "product_name",
COALESCE(SUM(CAST(( cc_data_view."purchase_revenue" ) AS DOUBLE PRECISION)), 0) AS "sum_of_purchase_revenue",
COALESCE(SUM(CAST(( cc_data_view."purchases" ) AS DOUBLE PRECISION)), 0) AS "sum_of_purchases"
FROM public.cc_data_view AS cc_data_view
WHERE ((( cc_data_view."daterange" ) >= (DATE_TRUNC('day', DATE '2023-01-01')) AND ( cc_data_view."daterange" ) < (DATE_TRUNC('day', DATE '2024-01-01'))))
GROUP BY
1
ORDER BY
2 DESC
FETCH NEXT 500 ROWS ONLY
data = %sql SELECT LOWER(product_category) AS `Product Category`, COUNT(*) AS EVENTS \
FROM cc_data_view \
WHERE daterange BETWEEN '2023-01-01' AND '2024-01-01' \
GROUP BY 1 \
ORDER BY `Events` DESC \
LIMIT 5;
display(data)
RStudio が BI 拡張機能を使用して生成するクエリには lower が含まれています。これは、カスタム変換が RStudio と BI 拡張機能によって実行されることを意味します。
code language-sql
SELECT "d2", COUNT(*) AS "n"
FROM (
SELECT "cc_data_view".*, lower("product_category") AS "d2"
FROM "cc_data_view"
WHERE ("daterange" >= '2023-01-01' AND "daterange" <= '2024-01-01')
) AS "q01"
GROUP BY "d2"
ORDER BY "d2"
LIMIT 1000
