Drizzle Team•8mo ago

Optimize query question/help

I have the current query that gets the total average of coffee consumption per day, there can be multiple logs with the same date. Wondering if using a subquery is the best way to do it? are there any helpers that can reduce the query size?

// schema
export const log = pgTable(
  "log",
  {
    id: serial("id").primaryKey(),
    date: date("date").notNull(),
    coffeeId: integer("coffeeId")
      .notNull()
      .references(() => coffee.id),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt"),
  },
  (columns) => ({
    dateIdx: index("date_idx").on(columns.date),
  }),
);

// schema
export const log = pgTable(
  "log",
  {
    id: serial("id").primaryKey(),
    date: date("date").notNull(),
    coffeeId: integer("coffeeId")
      .notNull()
      .references(() => coffee.id),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt"),
  },
  (columns) => ({
    dateIdx: index("date_idx").on(columns.date),
  }),
);

// query
 getAlltimeCoffeeAvg: publicProcedure.query(async ({ ctx }) => {
    const subquery = ctx.db
      .select({
        count: count(log.id).as("coffee_count"),
      })
      .from(log)
      .groupBy(log.date)
      .as("subquery");

    const avgTotal = await ctx.db
      .select({
        avg: avg(subquery.count),
      })
      .from(subquery);
    
    return avgTotal; // [{"avg":"1.5000000000000000"}]
  }),

// query
 getAlltimeCoffeeAvg: publicProcedure.query(async ({ ctx }) => {
    const subquery = ctx.db
      .select({
        count: count(log.id).as("coffee_count"),
      })
      .from(log)
      .groupBy(log.date)
      .as("subquery");

    const avgTotal = await ctx.db
      .select({
        avg: avg(subquery.count),
      })
      .from(subquery);
    
    return avgTotal; // [{"avg":"1.5000000000000000"}]
  }),

6 Replies

Sillvva•8mo ago

Since your date field is a date column, you can divide the total number of ids by the distinct number of dates.

const avgTotal = await db.select({
    avg: sql`count(${log.id}) / count(distinct ${log.date})`.mapWith(Number)
}).from(log)

const avgTotal = await db.select({
    avg: sql`count(${log.id}) / count(distinct ${log.date})`.mapWith(Number)
}).from(log)

Cmelvard•8mo ago

Oh I forgot you can do something like that although It doesn't seem to get the correct result, it brings back "1" instead of "1.714", I'll keep looking into using your idea, thanks !! count(distinct ${log.date}) brings out 7 count(${log.id}) brings out 12 which is correct, but it doesnt seem to be doing the division 🤔

Sillvva•8mo ago

I forgot you need to cast the first count as a decimal

const avgTotal = await db.select({
    avg: sql`count(${log.id})::decimal / count(distinct ${log.date})`.mapWith(Number)
}).from(log)

const avgTotal = await db.select({
    avg: sql`count(${log.id})::decimal / count(distinct ${log.date})`.mapWith(Number)
}).from(log)

Otherwise it converts to an integer

Cmelvard•8mo ago

oh interesting I was thinking it was something regarding that but didn't know the syntax, is there any docs to read about "::decimal" cast? it worked this time by the way, thank you!!

Sillvva•8mo ago

Count is cast as an integer by default https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-cast/

admin

PostgreSQL Tutorial

PostgreSQL CAST: Convert a Value of One Data Type to Another

You will learn how to use the PostgreSQL CAST() function and cast operator (::) to cast a value of one type to another.

Cmelvard•8mo ago

thanks!!

Gaming

Programming

Optimize query question/help