On Thu, Aug 3, 2023 at 4:25 AM Daniele Nicolodi <dani...@grinta.net> wrote:
> On 03/08/23 08:27, Eric Altendorf wrote:
> > When you do something like
> > SELECT account, SUM(position) ...
> >
> > you get a nice table where the list of positions for each account is
> > grouped (with expand=true, it renders like a nested set of rows within
> > the row) and each account name is only printed once.
>
> This is because the query compiler infers that you are running an
> aggregate query and thus adds an implicit 'GROUP BY account' to it.
>
Thanks, that's what I thought aggregation was doing but I wasn't sure.
> expand=true changes the representation of the inventory type returned by
> sum(position) from an horizontal one to a vertical one, but does not
> introduce any notion of grouping.
>
Right, I figured that was just a rendering feature.
> > When you do something like
> > SELECT id, date, narration, account, cost(position),
> units(position)...
> >
> > You get one row per posting, and one transaction's information (e.g.,
> > ie, date, narration) gets repeated for each row. Visually this just
> > makes it hard to see the grouping of postings in a transaction.
>
> A SELECT without an explicit FROM clause specifying a table is
> implicitly run over the #postings table, which is just a concatenation
> of the posting in all the transactions in the ledger.
>
Are you implying that if an explicit FROM clause specifies something else?
I thought all SELECT queries were selecting from the postings table. In my
case, I do happen to have a FROM clause, the full query is (now) more like:
SELECT date, narration, account, units(position) as amount,
round(number(cost(position)) / number(units(position)), 4) as cost_each,
cost(position) as total_cost FROM has_account("PnL") and quarter(date) =
"2021-Q1"
> Is there a way to visually group postings in the same transaction, the
> > way aggregated data is grouped when you do the SUM()? I'm not sure
> > where this logic would belong; one could have a generic string CONCAT()
> > aggregation operator I suppose, but that doesn't seem right, or one
> > could hack it as an option into render_text(), but that also seems
> awkward.
>
> The SELECT results set does not have any knowledge that the values in
> the columns belong to postings and that these postings belong to
> transactions. I don't see a way to implement what you are asking.
>
Yeah, I looked at the code and I think the cleanest way to do this would be
to have the query engine, in addition to returning the dtype for each
column, return a flag marking whether the column is sourced from a
transaction or a posting. This could be a separate flag or maybe you could
even work it into the existing `dtype` interface with a Generic as a marker
type, e.g., instead of putting `datetime.date` in dtype, since it comes
from the Transaction, you put in `TxCol[datetime.date]` for some `TxCol`
wrapper/marker Generic. (But I'm guessing there, I'm not a Python
expert.) The ColumnRenderer base class could unpack the nested type and
dispatch as normal, but also set a flag saying that column renderers for
TxCol types are eligible for ditto
<https://en.wikipedia.org/wiki/Ditto_mark> behavior. Then, render_text()
could take an "enable ditto" argument, and upon rendering, any ditto
columns are only rendered in full in their first seen row, otherwise they
are rendered as a ditto mark (or blank).
I don't think this would be horrible, but I would also understand if you
don't like the idea :)
To customize the rendering of the results set, you can implement your
> own beanquery.query_render.render_text() function. Postings are always
> iterated grouped by transaction, thus if you know that you are always
> going to have the 'id' column in the results set, you can just emit an
> empty line every time the value in this column changes.
>
Thanks, I had taken a quick look there, but I'll look more closely.
However, if you want to see postings grouped by transaction, maybe the
> PRINT statement is what you want, instead of the SELECT statement.
>
I don't think that will let me do what I want, but it's a good point I'd
forgotten about, so thanks for reminding me of that option.
thanks,
eric
>
> Cheers,
> Dan
>
> --
> You received this message because you are subscribed to the Google Groups
> "Beancount" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to beancount+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/beancount/fd01ff7f-7bae-bf87-fc62-4f900f5fbd2c%40grinta.net
> .
>
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to beancount+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/CAFXPr0toN13YyvAHG67Ufzd3ajieUop%2B-TPkpBR%2Bt43dHgbueQ%40mail.gmail.com.
