waitingkuo commented on issue #3048:
URL:
https://github.com/apache/arrow-datafusion/issues/3048#issuecomment-1210463229
43.
original query:
```
SELECT DATE_TRUNC('minute', EventTime) AS M, COUNT(*) AS PageViews FROM hits
WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND EventDate <=
'2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY
DATE_TRUNC('minute', EventTime) ORDER BY DATE_TRUNC('minute', EventTime) LIMIT
10 OFFSET 1000;
```
Since datafusion import columns as case-sensitive so we need to modify it as
```
SELECT DATE_TRUNC('minute', "EventTime") AS M, COUNT(*) AS PageViews FROM
hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-14' AND "EventDate" <=
'2013-07-15' AND "IsRefresh" = 0 AND "DontCountHits" = 0 GROUP BY
DATE_TRUNC('minute', "EventTime") ORDER BY DATE_TRUNC('minute', "EventTime")
LIMIT 10 OFFSET 1000;
```
EventTime is originally Int in parquet, we need to cast it (as parquet
importer doesn't allow us to add schema for now)
```
SELECT DATE_TRUNC('minute', to_timestamp_seconds("EventTime")) AS M,
COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND
"EventDate"::INT::DATE >= '2013-07-14' AND "EventDate"::INT::DATE <=
'2013-07-15' AND "IsRefresh" = 0 AND "DontCountHits" = 0 GROUP BY
DATE_TRUNC('minute', to_timestamp_seconds("EventTime")) ORDER BY
DATE_TRUNC('minute', to_timestamp_seconds("EventTime") LIMIT 10 OFFSET 1000;
```
The last isue is that we cannot `ORCER BY BY DATE_TRUNC('minute',
to_timestamp_seconds("EventTime")`
i fixed it as `ORCER BY BY DATE_TRUNC('minute', M)`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
