Group By on (very) large table

Hi,

I think the 3rd option is the best.

But I found the another issue is that there are too many duplicated records in your table.

Please refer to my steps below:

1.  create a table call competition_copy,

create table competition_copy as select * from competition;

2. remove duplicated records in competition_copy,

delete from competition_info_copy a where a.rowid > any (select b.rowid from competition_info_copy b where b.competition_number = a.competition_number
and b.competition_day = a.competition_day and b.customer_id = a.customer_id)

3. split competition_copy into severial smaller table such as :

create table competition_a as select * from competition_copy a where a.competition_number = 1;

create table competition_b as select * from competition_copy a where a.competition_number = 2;

Remember to add index for these columns that will be used for query frequently.

4. create an insert trigger for table competition.

When you insert a record into table competition, for example, the new records’ competition_number =1, competition_day = 3, customer_id = XX, Check if this record existed in competition_a table or not. If not , insert into competition_a table.

5. Now you can create a table to get your group by records by combine several smaller tables’ combined records

Table =
Union (
                Summarize(
                    Competition_a,
                    Competition_a[competition_number],
                    Competition_a[competition_day],
                    Competition_a[customer],
                    “count”, countax(competition_a, customer_id)
             ),

            Summarize(
                    Competition_b,
                    Competition_b[competition_number],
                    Competition_b[competition_day],
                    Competition_b[customer],
                    “count”, countax(competition_b, customer_id)
             ),

             ….
)

7.  Now you can create a measure to show your count of customers quickly. And it will be auto refreshed using your on-premise gateway.

Best Regards
Alex